Select max and min date from each group in SQL Server

smile
 
on Aug 07, 2021 01:28 AM
442 Views

I have a data like this

TagID

T_Date

Diagnosis

101

2021-08-01

Fever

101

2021-08-05

Fever

101

2021-08-08

Fever

102

2021-08-03

Fever

102

2021-08-07

Fever

I want to get output like following. Please how to get solution

TagID

T_Date [StartDate]

T_Date [LasttDate]

Days

101

2021-08-01

2021-08-08

8 Days

102

2021-08-03

2021-08-07

5 Dayssss

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 07, 2021 06:15 AM

Hi smile,

Refer below query.

SQL

DECLARE @Test AS TABLE(TagID INT,T_Date DATE,Diagnosis VARCHAR(10))
INSERT INTO @Test VALUES(101,'08/01/2021','Fever')
INSERT INTO @Test VALUES(101,'08/05/2021','Fever')
INSERT INTO @Test VALUES(101,'08/08/2021','Fever')
INSERT INTO @Test VALUES(102,'08/03/2021','Fever')
INSERT INTO @Test VALUES(102,'08/07/2021','Fever')

SELECT DISTINCT TagID,
	(SELECT MIN(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID) 'T_Date [StartDate]',
	(SELECT MAX(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID) 'T_Date [LasttDate]',
	CONCAT(DATEDIFF(
		DAY,
		(SELECT MIN(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID),
		(SELECT MAX(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID)
	) + 1,' Days') 'Days'
FROM @Test t

Screenshot

TagID T_Date [StartDate] T_Date [LasttDate] Days
101 08/01/2021 08/08/2021 8 Days
102 08/03/2021 08/07/2021 5 Days