CODEWITHSUNDEEP
sql-servert-sql
jaguin
jaguin

Reputation: 63

TSQL - Iterate through list of dates, group data, and insert into table

So I have data being imported into a table in the following format. For any months missing, assume the MM, App_Amt, ClaimCnt, and UnqPtCnt columns are 0:

location|health_plan| plan_type | pcp |  MM  |App_Amt|ClaimCnt|UnqPtCnt|Date
SoJ     |BLUE SHIELD|    COM    | 2384|  18  | 0.00  |   0    |   0    |2014-01-01
SoJ     |BLUE SHIELD|    COM    | 2384|  20  |1430.08|   1    |   1    |2014-02-01
SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 468.69|   4    |   3    |2014-03-01
SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 184.12|   2    |   2    |2014-04-01
SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 184.12|   2    |   2    |2014-05-01
SoJ     |BLUE SHIELD|    COM    | 2384|  15  |2321.79|   3    |   3    |2014-06-01
SoJ     |BLUE SHIELD|    COM    | 2384|  18  | 258.18|   2    |   2    |2014-07-01
SoJ     |BLUE SHIELD|    COM    | 2384|  22  | 258.18|   2    |   2    |2014-08-01
SoJ     |BLUE SHIELD|    COM    | 2384|  83  | 395.29|   3    |   2    |2014-09-01
SoJ     |BLUE SHIELD|    COM    | 2384|  28  | 258.18|   2    |   2    |2014-10-01
SoJ     |BLUE SHIELD|    COM    | 2384|  32  | 571.02|   4    |   4    |2014-11-01
SoJ     |BLUE SHIELD|    COM    | 2384|  14  | 258.18|   2    |   2    |2014-12-01

I need to calculate a rolling/trailing 12 months based on this dataset. I will take the average of MM, sum of App_Amt, sum of ClaimCnt, and sum of UnqPtCt and group by location, health_plan, plan_type, and PCP and I have a list of date ranges I want to iterate through:

I am getting this list of dates from the source table itself. It currently has the 1st of the month from the last 3 years. Next month, the data will shift over one month. For example, right now it has a minimum date of 01/01/2014 and maximum date of 11/01/2016 but next month in April the minimum date will be 02/01/2014 and maximum date will be 12/01/2016.

declare @end_dt date
set @end_dt = dateadd(month,-4,getdate())

select distinct [date] as start_dt,DATEADD(month,11,[date]) end_dt from [dbo].[OON_Summary]

select * from @Rolling where end_dt <= @end_dt


 start_dt | end_dt
2014-01-01|2014-12-01
2014-02-01|2015-01-01
2014-03-01|2015-02-01
2014-04-01|2015-03-01
2014-05-01|2015-04-01
2014-06-01|2015-05-01
2014-07-01|2015-06-01
2014-08-01|2015-07-01
2014-09-01|2015-08-01
2014-10-01|2015-09-01
2014-11-01|2015-10-01
2014-12-01|2015-11-01
2015-01-01|2015-12-01
2015-02-01|2016-01-01
2015-03-01|2016-02-01
2015-04-01|2016-03-01
2015-05-01|2016-04-01
2015-06-01|2016-05-01
2015-07-01|2016-06-01
2015-08-01|2016-07-01
2015-09-01|2016-08-01
2015-10-01|2016-09-01
2015-11-01|2016-10-01
2015-12-01|2016-11-01

So far I have this simple query but it's far too manual and I know there must be a better way to automate this.

declare @start_dt date 
declare @end_dt date 
set @start_dt = '2014-01-01'
set @end_dt = '2014-12-01'

insert into [dbo].[OON_Rolling12]
  SELECT 
  ,[location]
  ,[health plan]
  ,[plan_type]
  ,[pcp]
  ,AVG([MM]) [MM]
  ,SUM([App_Amt]) [App_Amt]
  ,SUM([ClaimCnt]) [ClaimCnt]
  ,SUM([UnqPtCnt]) UnqPtCnt
  ,@end_dt as AsOfDate
FROM [dbo].[OON_Summary]
WHERE [Date] >= @start_dt AND [Date] <= @end_dt
GROUP BY 
   [location]
  ,[health plan]
  ,[plan_type]
  ,[pcp]

Running the query once will produce the first line in the output below. Essentially I am grouping all of the data from Jan 1 2014 through Dec 1 2014 and calling it Dec 2014 data. How can I iterate through that list of dates and generate the rest of the rows automatically?

location|health_plan| plan_type | pcp |  MM  |App_Amt|ClaimCnt|UnqPtCnt |AsOfDate
SoJ     |BLUE SHIELD|    COM    | 2384|  25  |6565.83|   27   |   25    |2014-12-01
SoJ     |BLUE SHIELD|    COM    | 2384|  24  |6565.83|   27   |   25    |2015-01-01
SoJ     |BLUE SHIELD|    COM    | 2384|  22  |5135.75|   26   |   24    |2015-02-01
SoJ     |BLUE SHIELD|    COM    | 2384|  20  |4689.06|   22   |   21    |2015-03-01
SoJ     |BLUE SHIELD|    COM    | 2384|  19  |4504.94|   20   |   19    |2015-04-01
SoJ     |BLUE SHIELD|    COM    | 2384|  17  |4320.82|   18   |   17    |2015-05-01
SoJ     |BLUE SHIELD|    COM    | 2384|  16  |1999.03|   15   |   14    |2015-06-01
SoJ     |BLUE SHIELD|    COM    | 2384|  14  |1740.85|   13   |   12    |2015-07-01
SoJ     |BLUE SHIELD|    COM    | 2384|  13  |1482.67|   11   |   10    |2015-08-01
SoJ     |BLUE SHIELD|    COM    | 2384|  6   |1087.38|    8   |   8     |2015-09-01
SoJ     |BLUE SHIELD|    COM    | 2384|  3   |829.20 |    6   |   6     |2015-10-01
SoJ     |BLUE SHIELD|    COM    | 2384|  0   |258.18 |    2   |   2     |2015-11-01    
SoJ     |BLUE SHIELD|    COM    | 2384|  0   | 0.00  |    0   |   0     |2015-12-01

Upvotes: 1

Views: 160

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use OUTER APPLY to get the aggregated values for the last 12 months.

SELECT 
   o1.[location]
  ,o1.[health plan]
  ,o1.[plan_type]
  ,o1.[pcp]
  ,o2.[MM]
  ,o2.[App_Amt]
  ,o2.[ClaimCnt]
  ,o2.[UnqPtCnt]
FROM [dbo].[OON_Summary] o1
OUTER APPLY (SELECT AVG([MM]) as [MM]
                   ,SUM([App_Amt]) as [App_Amt]
                   ,SUM([ClaimCnt]) as [ClaimCnt]
                   ,SUM([UnqPtCnt]) as UnqPtCnt
             FROM [dbo].[OON_Summary] o2
             WHERE o1.[location]=o2.[location] AND o1.[health plan]=o2.[health plan] 
             AND o1.[plan_type]=o2.[plan_type] AND o1.[pcp]=o2.[pcp]
             AND o2.[date] BETWEEN DATEADD(month,-11,o1.[date]) AND o1.[date]
             ) o2

This is slightly easier in sql server versions 2012+ with window functions.

Upvotes: 1

Related Questions