Dynamic 12 month rolling total for each previous month, going back 12 months

Question

I have the following sql Code (where clause just to limit rows currently)

select 
    month,
    monthname,
    year,
    count(distinct case when a.dim_service_type_id_desc like '%Direct Payment%' then a.DIM_PERSON_ID else null end) as No_dp,
    count(distinct a.DIM_PERSON_ID) as no_ppl
from   
    SERVICE_PROVISIONS a
    inner join date_tbl d on CONVERT(VARCHAR(35),a.start_dttm,112) = d.dim_date_id
where 
    a.dim_person_id >0
    and year = 2018
group by 
    month,
    monthname,
    year

my output is this

month monthname     year    No_dp   no_ppl
1       January     2018    142     1604
2       February    2018    111     1526
3       March       2018    133     1636
4       April       2018    1107    3829
5       May         2018    140     1575
6       June        2018    131     1389
7       July        2018    200     893
8       August      2018    2       73
9       September   2018    1       32
10      October     2018    2       21
11      November    2018    2       21
12      December    2018    2       19

So my question is - the customer wants to see how many services were open (using start date and end date) during the previous 12 months (not how many were started, but how many were current and not ended). This is fine when using the current month, however they want to show this also for the previous 12 months as a rolling dynamic figure.

So for example this month in July they want to see how many services were open during the last 12 months. Last month June, they want to see how many services were open during the 12 months previous to June and so on for the previous 12 months.

The table needs to have the month name for the last 12 months and in a column show the number of services that were open in the previous 12 months next to that month.

I hope that makes sense, sorry if it doesn't, feel free to ask questions and I will try to clarify.

The output needs to look something like the current output table, but it is currently only showing how many services were started within that month, which isn't what we want.

The date table is a reference table which has different date formats etc. It can be used or added to if needed.

Answer 1

I've had to make several assumptions about your data. Hopefully the query I'll show in a minute will be easy for you to adjust if any of these are wrong:

1. I am guessing by its name that start_dttm is a datetime or datetime2 column.
2. I assume there is a corresponding column called end_dttm that gives the end date/time of a service, and that a null in this column would indicate that a service has not yet ended.
3. My best guess as to what it means for a service to be "open" in a given month is that it began sometime either within or prior to that month, and has not ended by the time that month is over.
4. I assume from your original query that multiple services having the same dim_person_id do not represent distinct services.

Since I don't know what's in your date_tbl, I'll show an example that doesn't require it. Consider the following query:

select
    BeginDate = dateadd(month, -1, dateadd(day, 1, eomonth(getdate(), -Offset.X))),
    EndDate = dateadd(day, 1, eomonth(getdate(), -Offset.X))
from
    (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) Offset(X)

This will give you 12 records, representing the current month and each of the 11 preceding months. Note that my EndDate here is not actually the last day of the month, but the first day of the following month. I've done this because of assumption 1 above; since your service dates may have time components, I'll determine whether they fall in a given month by checking if their dates are strictly earlier than the start of the following month. Here's what that query gives me:

BeginDate   EndDate
2018-07-01  2018-08-01
2018-06-01  2018-07-01
2018-05-01  2018-06-01
2018-04-01  2018-05-01
2018-03-01  2018-04-01
2018-02-01  2018-03-01
2018-01-01  2018-02-01
2017-12-01  2018-01-01
2017-11-01  2017-12-01
2017-10-01  2017-11-01
2017-09-01  2017-10-01
2017-08-01  2017-09-01

Now I'll join the above result set to your SERVICE_PROVISIONS data, looking for records in each month that have dim_person_id > 0 (from your original query) and which satisfy assumption 3 above.

-- Some sample data (assumptions 1 & 2)
declare @SERVICE_PROVISIONS table (dim_person_id bigint, start_dttm datetime, end_dttm datetime);
insert @SERVICE_PROVISIONS values
    (1, '20180101', '20180315'),
    (1, '20180101', '20180315'),
    (2, '20171215', '20180520');

-- The CTE defines the months we'll report on, as described earlier.
with MonthsCTE as
(
    select
        BeginDate = dateadd(month, -1, dateadd(day, 1, eomonth(getdate(), -Offset.X))),
        EndDate = dateadd(day, 1, eomonth(getdate(), -Offset.X))
    from
        (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) Offset(X)
)

-- This query matches the months from the CTE against the applicable services.
select
    [Month] = datepart(month, M.BeginDate),
    [MonthName] = datename(month, M.BeginDate),
    [Year] = datepart(year, M.BeginDate),
    ServicesOpen = count(distinct S.dim_person_id)   -- Assumption 4
from
    MonthsCTE M
    left join @SERVICE_PROVISIONS S on
        S.dim_person_id > 0 and
        S.start_dttm < M.EndDate and   -- Assumption 3
        (
            S.end_dttm >= M.EndDate or
            S.end_dttm is null   -- Assumption 2
        )
group by
    M.BeginDate,
    M.EndDate
order by
    M.BeginDate;

Note that I moved the dim_person_id > 0 from the WHERE clause to the JOIN so that each of the 12 months will still appear in the result set even if there were no services open during that time. Results:

Month   MonthName   Year   ServicesOpen
8       August      2017   0
9       September   2017   0
10      October     2017   0
11      November    2017   0
12      December    2017   1
1       January     2018   2
2       February    2018   2
3       March       2018   1
4       April       2018   1
5       May         2018   0
6       June        2018   0
7       July        2018   0

Answer 2

something a bit like this - if you can write a query to get the value you want for a row in your ootput, then use cross apply to link to that query. Counting records that have an open record before the month, but no close record before the month seems feasible

SELECT IQ. *, OA.SERVICE_PROVISIONS FROM (select 
month,
monthname,
year,
a.dim_person_id dim_person_id,
count(distinct case when a.dim_service_type_id_desc like '%Direct Payment%' then a.DIM_PERSON_ID else null end) as No_dp,
count(distinct a.DIM_PERSON_ID) as no_ppl

from   
SERVICE_PROVISIONS a
inner join date_tbl d on CONVERT(VARCHAR(35),a.start_dttm,112) = d.dim_date_id

where 
a.dim_person_id >0
and year = 2018

group by 
month,
monthname,
year) IQ
CROSS APPLY 
(SELECT count(0) OpenThings FROM SERVICE_PROVISIONS SP1 WHERE 
                 (sp1.startdate < DATEFROMPARTS(IQ.year,iq.month,1) 
                AND 
                sp1.enddate is null or sp1.enddate > DATEFROMPARTS(IQ.year,iq.month,1)) and sp1.dim_person_id = iq.dim_person_id
) AS OA