I have a table that stores patient lab test results. There can be results from multiple tests like Albumin, Potassium, Phosphorus etc. First reading for each patient from each of these categories is stored in a table called #MetricFirstGroupReading.
CREATE TABLE #MetricFirstGroupReading (Patient_Key INT, Metric_Group VARCHAR(100), Observation_Date DATE) ALTER TABLE #MetricFirstGroupReading ADD CONSTRAINT UQ_MetricFirst UNIQUE (Patient_Key, Metric_Group); INSERT INTO #MetricFirstGroupReading SELECT 1, 'Albumin', '2018-11-15' UNION SELECT 1, 'Potassium', '2018-12-10' UNION SELECT 2, 'Albumin', '2018-10-20' UNION SELECT 2, 'Potassium', '2018-11-25'
Now, I need to populate all month end dates upto current month into a new table, for each record from the #MetricFirstGroupReading table. Following is the expected result when the query run on December 2018.
I know how to do it using WHILE loops. How to do this without loops, using set based SQL queries, in SQL Server 2016?
Advertisement
Answer
Following worked. This is an expansion of the idea present in tsql: How to retrieve the last date of each month between given date range
Query
CREATE TABLE #AllMonthEnds (MonthEndDate DATE) DECLARE @Start datetime DECLARE @End datetime SELECT @Start = '2000-01-01' SELECT @End = DATEADD(MONTH,1,GETDATE()) ;With CTE as ( SELECT @Start as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last] UNION ALL SELECT Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE WHERE Date<@End ) INSERT INTO #AllMonthEnds SELECT [Date] FROM CTE WHERE [Last]=1 OPTION ( MAXRECURSION 0 ) SELECT T.Patient_Key, T.Metric_Group, T.Observation_Date AS First_Observation_Date, DATEDIFF(MONTh,Observation_Date, MonthEndDate) AS MonthDiff, A.MonthEndDate AS IterationDate FROM #AllMonthEnds A INNER JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Patient_Key, Metric_Group ORDER BY Observation_Date) AS RowVal FROM #MetricFirstGroupReading M )T ON A.MonthEndDate >= T.Observation_Date WHERE RowVal = 1 ORDER BY Patient_Key, Metric_Group, T.Observation_Date, A.MonthEndDate