Set based query to replace loop to populate all month end dates from given date for all records

Question

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?

Answer 1

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

Answer 2

How about:

    select MetricFirstGroupReading.*, datediff(month, MetricFirstGroupReading.Observation_Date, months.monthendval) monthdiff, months.* 
    into allmonths
    from
    (
    SELECT 1 patientid, 'Albumin' test, '2018-11-15' Observation_Date UNION
    SELECT 1 patientid, 'Potassium' test, '2018-12-10' Observation_Date UNION
    SELECT 2 patientid, 'Albumin' test, '2018-10-20' Observation_Date UNION
    SELECT 2 patientid, 'Potassium' test, '2018-11-25' Observation_Date) MetricFirstGroupReading 
join 
    (
    select '2018-10-31' monthendval union
    select '2018-11-30' monthendval union
    select '2018-12-31' monthendval 
    ) months on MetricFirstGroupReading.Observation_Date< months.monthendval

Replace the first select union with your table, and add or remove month ends from the second inner select.

Answer 3

Consider building a temp table of all 12 month end dates, then join to main table by date range. Use DateDiff for month difference:

CREATE TABLE #MonthEndDates (Month_End_Value DATE)

INSERT INTO #MonthEndDates
VALUES ('2018-01-31'),
       ('2018-02-28'),
       ('2018-03-31'),
       ('2018-04-30'),
       ('2018-05-31'),
       ('2018-04-30'),
       ('2018-06-30'),
       ('2018-07-31'),
       ('2018-08-31'),
       ('2018-09-30'),
       ('2018-10-31'),
       ('2018-11-30'),
       ('2018-12-31')

SELECT m.Patient_Key, m.Metric_Group, m.Observation_Date, 
       DateDiff(month, m.Observation_Date, d.Month_End_Value) AS Month_Diff, 
       d.Month_End_Value

FROM #MetricFirstGroupReading m
INNER JOIN #MonthEndDates d
  ON m.Observation_Date < d.Month_End_Value

GO

Rextester Demo