Database

CREATE TABLE #Person
(
	ID INT NOT NULL,
	DOB DATE NOT NULL
);

CREATE TABLE #PersonName
(
	ID INT NOT NULL,
	PID INT NOT NULL,
	FNAME VARCHAR(50) NOT NULL,
	MNAME VARCHAR(10) NULL,
	LNAME VARCHAR(50) NOT NULL,
	NAMETYPE CHAR(1) NOT NULL
);

INSERT INTO #Person
(ID, DOB)
VALUES 
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');

INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');

WITH L AS
(
	SELECT *
	FROM #PersonName
	WHERE NAMETYPE = 'L'
),
C AS
(
	SELECT *
	FROM #PersonName
	WHERE NAMETYPE = 'C'
)
SELECT P.ID,
	L.FNAME AS FNAME_L,
	L.MNAME AS MNAME_L,
	L.LNAME AS LNAME_L,
	C.FNAME AS FNAME_C,
	C.MNAME AS MNAME_C,
	C.LNAME AS LNAME_C	
FROM #Person P
LEFT JOIN L
	ON L.PID = P.ID
LEFT JOIN C
	ON C.PID = P.ID;
	
DROP TABLE #PersonName;
DROP TABLE #Person;

Roger Wright wrote:

use a single table date-meterID-HE_n value

Roger Wright wrote:

Worse still would be to use a single table date-meterID-HE_n value.
That would entail a huge number of records with little content, hardly a useful
structure.

SELECT * FROM FirstTable 
EXCEPT 
SELECT * FROM SecondTable 

SELECT * FROM SecondTable 
EXCEPT 
SELECT * FROM FirstTable 

Mika Wendelius wrote:

- correct indexing strategy must be implemented (depending on the needs)
- I wouldn't use separate tables since it would affect program logic
- think carefully about the table structure, optimize it for typical use-cases

Mika Wendelius wrote:

consider using partitioning.

Hum Dum wrote:

I think about moving data to separate table as query which has to run on table,
will need only information of 1 previous day , today, 1 after day on any given
day.

Hum Dum wrote:

If apply all these will it still work after 5 year (assuming 12000 * 12 * 365 *
5 rows) ?

Hum Dum wrote:

don't have any clue about this?
Will be helpful if you point to an article
(targeting less then have DBA exp)