What I need to do is to be able to extract per ID, the first 'Level' based on the earliest Date and also the Highest Level relating to the same ID. So for ID 123, the First Level will be "2" and the Highest would be "7". Ideally the output should be like this:
ID First Level Highest Level
123 2 7
If anyone can point me in the direction I need to go with this, that would be great
SELECT
a.id,
b.[First Level],
MAX(a.Level) AS [Highest Level]
FROM
Tbl a
CROSS APPLY
(
SELECT TOP (1) Level AS [First Level]
FROM Tbl b
WHERE b.Id = a.Id
ORDER BY b.Date ASC
) AS b
GROUP BY
a.id,
b.[First Level]
-- *** Test Date in Consumable Format *** ---
-- Please provide in future
CREATE TABLE #t
(
ID int NOT NULL
,Level int NOT NULL
,Date date
);
INSERT INTO #t
VALUES (123, 1, '20150201')
,(123, 2, '20150103')
,(123, 7, '20150703')
,(456, 0, '20140927')
,(456, 2, '20150130')
,(78749, 2, '20141201')
,(78749, 3, '20150303');
-- *** End Test Data ***
-- Option 1
WITH MinMax
AS
(
SELECT ID
,MIN(Date) AS MinDate
,MAX(Date) AS MaxDate
FROM #t
GROUP BY ID
)
SELECT M.ID, I.Level, A.Level
FROM MinMax M
JOIN #t I
ON M.ID = I.ID
AND M.MinDate = I.Date
JOIN #t A
ON M.ID = A.ID
AND M.MaxDate = A.Date;
-- Option 2 (Might be more efficient)
WITH MinMax
AS
(
SELECT *
,MIN(Date) OVER (PARTITION BY ID) AS MinDate
,MAX(Date) OVER (PARTITION BY ID) AS MaxDate
FROM #t
)
SELECT ID
,MIN(CASE WHEN Date = MinDate THEN Level END) AS FirstLevel
,MIN(CASE WHEN Date = MaxDate THEN Level END) AS LastLevel
FROM MinMax
GROUP BY ID;