Re: Partition rows by id and DateColumn or DateColumn + 1

thorrocks 

To get the results you want (as I understand it), you need to add a process after finding rows treated as one. Use the window function OVER to partition the rows by PatientID and DischargeDate, then use the aggregate function MIN to replace DaysToFollowUp value in each partitions as you need. Finally use the process I mentioned earlier to get the results. Here is code:

WITH HT (PatientId, AdmitDate, AdmitId, DischargeDate, DaysToFollowUp)
AS
(
	SELECT PatientId, AdmitDate, ROW_NUMBER() OVER (ORDER BY PatientId, AdmitDate), DischargeDate, DaysToFollowUp 
	FROM #HospitalStays
	WHERE DATEADD(day, 1, AdmitDate) NOT IN (SELECT AdmitDate FROM #HospitalStays)
	UNION ALL
	SELECT HT.PatientId, HS.AdmitDate, HT.AdmitId, HS.DischargeDate, HS.DaysToFollowUp
	FROM #HospitalStays HS
	INNER JOIN HT
	ON 
		HS.PatientId = HT.PatientId
		AND DATEADD(day, 1, HS.AdmitDate) = HT.AdmitDate
)
SELECT PatientID
, MIN(AdmitDate) AS AdmitDate
, MAX(DischargeDate) AS DischargeDate
, MAX(DaysToFollowUp) AS DaysToFollowUp    --Get max or min value
FROM
(
	SELECT PatientID, 
	AdmitDate,
	AdmitId,
	DischargeDate AS DischargeDate, 
	MIN(DaysToFollowUp) OVER (PARTITION BY PatientID, DischargeDate) AS DaysToFollowUp  -- Get minimal DaysToFollowUp in partition which has same patienID and DischargeDate
	FROM HT
)A
GROUP BY PatientID, AdmitId