CODEWITHSUNDEEP
sql-server
user3749447
user3749447

Reputation: 299

SQL Server: How to return value for each past 6 months

I have a query that returns a score on whether or not 3 of the columns = 1 and then if the ProviderID exists in the 2nd table. I need to be able to return a score for each month for 6 months using column Time_Stamp, not including current month. The below returns the score for last month. How can I include the remaining 5 months and ROW_NUMBER() them?

DECLARE @ProviderID int = '1717';

WITH cte as
(
    SELECT TOP 1 
       a.ProviderID, Time_Stamp,
       SUM(CASE WHEN [AdditionalReports] = '1' THEN 5 ELSE 0 END) as AdditionalReports,
       SUM(CASE WHEN [UniqueReportRequests] = '1' THEN 15 ELSE 0 END) as UniqueReportsRequests,
       SUM(CASE WHEN [SurveyCompleted] = '1' THEN 30 ELSE 0 END) as SurveyCompleted,
       MAX(CASE WHEN b.ProviderID IS NULL THEN 0 ELSE 50 END) as SubscriptionExists
    FROM 
       ProviderValueCard a
    LEFT JOIN 
       SubscriptionsTV b ON a.ProviderID = b.ProviderID  
    WHERE 
       a.ProviderID = @ProviderID AND GroupID = 2
       AND Time_Stamp BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND  DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
    GROUP BY 
       Time_Stamp, a.ProviderID, event
    ORDER BY 
        event DESC, Time_Stamp DESC
)
SELECT 
   ProviderID, Time_Stamp, 
   (AdditionalReports + UniqueReportsRequests + SurveyCompleted + SubscriptionExists) AS TotalScore
FROM 
   cte

Here is how to grab the first/last day of previous months:

2 months ago:

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0) as FirstD2monthsago,
        DATEADD(DAY, -DAY(GETDATE()), DATEADD(MONTH, -1, GETDATE())) AS last_day_2_months_ago

3 months ago etc:

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0) as FirstD3monthsago,
        DATEADD(DAY, -DAY(GETDATE()), DATEADD(MONTH, -2, GETDATE())) AS last_day_3_months_ago

Desired output

ProviderID  Time_Stamp              TotalScore  Row_Number
----------- ----------------------- -----------
1717        2014-08-29 12:11:17.610 70              1
1717        2014-07-29 12:11:17.610 95              2
1717        2014-06-29 12:11:17.610 100             3
1717        2014-05-29 12:11:17.610 70              4
1717        2014-04-29 12:11:17.610 70              5
1717                                                6

Upvotes: 0

Views: 314

Answers (2)

Jim V.
Jim V.

Reputation: 2177

DECLARE @ProviderID INT, @Now DATETIME, @Months INT
SELECT @ProviderID  = 1717, @Now = GETDATE(), @Months = 5

WITH 
date_range_cte AS (
    SELECT 1 AS RowNum, DATEADD(mm,-1,@Now) AS StartDate, DATEADD(mm,0,@Now) AS EndDate
    UNION ALL
    SELECT d.RowNum + 1 AS RowNum, DATEADD(mm,(-d.RowNum - 1),@Now) AS StartDate, DATEADD(mm,-d.RowNum,@Now) AS EndDate
    FROM date_range_cte d
    WHERE d.RowNum + 1 <= @Months
    ),
    main_cte AS (
            SELECT 
            ROW_NUMBER() OVER (PARTITION BY a.ProviderID, d.RowNum, d.StartDate ORDER BY Time_Stamp DESC) AS ordinal_position,
            a.ProviderID, 
            d.RowNum, 
            d.StartDate,
            [AdditionalReports] * 5 AS AdditionalReports,
            [UniqueReportRequests] * 15 AS UniqueReportsRequests,
            [SurveyCompleted] * 30 as SurveyCompleted,
            CASE WHEN b.ProviderID IS NULL THEN 0 ELSE 50 END as SubscriptionExists
            FROM ProviderValueCard a
            INNER JOIN date_range_cte d ON d.StartDate < Time_Stamp AND Time_Stamp <= d.EndDate 
            LEFT OUTER JOIN SubscriptionsTV b ON a.ProviderID = b.ProviderID  
            WHERE a.ProviderID = @ProviderID AND GroupID = 2
            )  
        SELECT ProviderID, RowNum, StartDate, (AdditionalReports + UniqueReportsRequests + SurveyCompleted + SubscriptionExists) AS TotalScore
        FROM main_cte
        WHERE ordinal_position = 1
        ORDER BY RowNum

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

Here's a couple of ways (psuedocode):

1 - Make a cte just like your existing one for each month you want to get. The only thing you need to change in each one is this line:

AND Time_Stamp BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND  DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

For 2 months ago, you would change it to this:

AND Time_Stamp BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0) AND  DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

and so on for the other months back.

2 - Referring to the same line in your cte above, change it to a -6 to get data for the past 6 months. Then include a MONTH(TimeStamp) column in your select list and group by it to get one row per month for the past 6 months.

Upvotes: 0

Related Questions