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 |
