Sql-server – Counting Grouped records, using start date and end date with NULLs

azure-sql-databasedatesql server

Here is my issue, Source DB is Azure MSSQL – I have a table that lists all the subscriptions sold. The Relevant columns are:

Account Number, Subscription Group, Purchase Date, Cancellation Date

Cancellation Date contains NULL values (for Subscriptions that have yet to be cancelled). An Account may have multiple subscriptions, across multiple subscription groups.

Sample data is:

Account number | Subscription group | Purchase date | Cancellation date |

1 | A | 2/4/2013 | 2/4/2014 |
1 | A | 2/4/2013 | NULL |
1 | B | 2/4/2013 | NULL |
2 | C | 5/7/2018 | 5/8/2020 |
3 | C | 20/3/2020 | NULL |
3 | C | 4/8/2020 | NULL |

Etc.

I am looking to count the number of Subscription Groups for each customer and for each month – so the output would look something like this:

Customer | Count of A | Count of B | Count of C | Month/Year |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

I can write the query for a given month:

select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t.[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
Group by t.[Account Number], [Month/Year]

However, I'm hoping someone can help me modify the above so it will run for All months (based on MIN from Purchase date and MAX on Cancellation date)

Best Answer

I ended up doing this using a Stored Procedure, creating a Temp Table:

DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')      
         ,@EndDate  = (select max([Cancellation date]) from Table);


;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

Then doing a Loop through that Table and populating an output table:

Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) > 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable

Query:

SELECT DISTINCT its.id
    , Start_Date = its.Start_Date 
    , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
    --, x1=itmax.End_Date, x2=itmin.Start_Date, x3=its.End_Date
FROM @Items its
OUTER APPLY (
    SELECT Start_Date = MAX(End_Date) FROM @Items std
    WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
) itmin
OUTER APPLY (
    SELECT End_Date = MIN(Start_Date) FROM @Items std
    WHERE std.Item_ID <> its.Item_ID+1000 AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
) itmax;

Output:

id  | Start_Date                    | End_Date                      
0   | 2015-01-23 00:00:00.0000000   | 2015-01-23 00:00:00.0000000   => 1
0   | 2015-01-24 00:00:00.0000000   | 2015-01-27 00:00:00.0000000   => 4
0   | 2015-01-29 00:00:00.0000000   | 2015-01-30 00:00:00.0000000   => 2
1   | 2016-01-20 00:00:00.0000000   | 2016-01-22 00:00:00.0000000   => 3
1   | 2016-01-23 00:00:00.0000000   | 2016-01-24 00:00:00.0000000   => 2
1   | 2016-01-25 00:00:00.0000000   | 2016-01-29 00:00:00.0000000   => 5

If you use these Start Date and End Date with DATEDIFF:

SELECT DATEDIFF(day
    , its.Start_Date 
    , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
) + 1
...

Output (with duplicates) is:

1, 4 and 2 for id 0 (your sample => SUM=7)
3, 2 and 5 for id 1 (Ypercube sample => SUM=10)

You then only need to put everything together with a SUM and GROUP BY:

SELECT id 
    , Days = SUM(
        DATEDIFF(day, Start_Date, End_Date)+1
    )
FROM (
    SELECT DISTINCT its.id
         , Start_Date = its.Start_Date 
        , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
    FROM @Items its
    OUTER APPLY (
        SELECT Start_Date = MAX(End_Date) FROM @Items std
        WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
    ) itmin
    OUTER APPLY (
        SELECT End_Date = MIN(Start_Date) FROM @Items std
        WHERE std.Item_ID <> its.Item_ID AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
    ) itmax
) as d
GROUP BY id;

Output:

id  Days
0   7
1   10

Data used with 2 different ids:

INSERT INTO @Items
    (id, Item_ID, Start_Date, End_Date)
VALUES 
    (0, 20009, '2015-01-23', '2015-01-26'),
    (0, 20010, '2015-01-24', '2015-01-24'),
    (0, 20011, '2015-01-23', '2015-01-26'),
    (0, 20012, '2015-01-23', '2015-01-27'),
    (0, 20013, '2015-01-23', '2015-01-27'),
    (0, 20014, '2015-01-29', '2015-01-30'),

    (1, 20009, '2016-01-20', '2016-01-24'),
    (1, 20010, '2016-01-23', '2016-01-26'),
    (1, 20011, '2016-01-25', '2016-01-29')

Sql-server – Group daily schedule into [Start date; End date] intervals with the list of week days

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic.

SET DATEFIRST 1 -- Make Monday weekday=1

DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY,                   -- Incremental uninterrupted sequence in the right order
                       ID int NOT NULL UNIQUE, ContractID int NOT NULL, dt date,  -- Original relevant values (ID is not really necessary)
                       WeekNo int NOT NULL, dowBit int NOT NULL);                 -- Useful to find gaps in days or weeks
INSERT INTO @Ranked
SELECT ID, ContractID, dt,
       DATEDIFF(WEEK, '1900-01-01', DATEADD(DAY, 1-DATEPART(dw, dt), dt)) AS WeekNo,
       POWER(2, DATEPART(dw, dt)-1) AS dowBit
FROM @Src
ORDER BY ContractID, WeekNo, dowBit

/*
Each evaluated date makes part of the carried sequence if:
  - this is not a new contract, and
    - sequence started this week, or
    - same day last week was part of the sequence, or
    - sequence started last week and today is a lower day than the accumulated weekdays list
  - and there are no sequence gaps since previous day
(otherwise it does not make part of the old sequence, so it starts a new one) */

DECLARE @RankedRanges TABLE (RowID int NOT NULL PRIMARY KEY, WeekDays int NOT NULL, StartRowID int NULL);

WITH WeeksCTE AS -- Needed for building the sequence gradually, and comparing the carried sequence (and previous day) with a current evaluated day
( 
    SELECT RowID, ContractID, dowBit, WeekNo, RowID AS StartRowID, WeekNo AS StartWN, dowBit AS WeekDays, dowBit AS StartWeekDays
    FROM @Ranked
    WHERE RowID = 1 
    UNION ALL
    SELECT RowID, ContractID, dowBit, WeekNo, StartRowID,
           CASE WHEN StartRowID IS NULL THEN StartWN ELSE WeekNo END AS WeekNo,
           CASE WHEN StartRowID IS NULL THEN WeekDays | dowBit ELSE dowBit END AS WeekDays,
           CASE WHEN StartRowID IS NOT NULL THEN dowBit WHEN WeekNo = StartWN THEN StartWeekDays | dowBit ELSE StartWeekDays END AS StartWeekDays
    FROM (
        SELECT w.*, pre.StartWN, pre.WeekDays, pre.StartWeekDays,
               CASE WHEN w.ContractID <> pre.ContractID OR     -- New contract always break the sequence
                         NOT (w.WeekNo = pre.StartWN OR        -- Same week as a new sequence always keeps the sequence
                              w.dowBit & pre.WeekDays > 0 OR   -- Days in the sequence keep the sequence (provided there are no gaps, checked later)
                              (w.WeekNo = pre.StartWN+1 AND (w.dowBit-1) & pre.StartWeekDays = 0)) OR -- Days in the second week when less than a week passed since the sequence started remain in sequence
                         (w.WeekNo > pre.StartWN AND -- look for gap after initial week
                          w.WeekNo > pre.WeekNo+1 OR -- look for full-week gaps
                          (w.WeekNo = pre.WeekNo AND                            -- when same week as previous day,
                           ((w.dowBit-1) ^ (pre.dowBit*2-1)) & pre.WeekDays > 0 -- days between this and previous weekdays, compared to current series
                          ) OR
                          (w.WeekNo > pre.WeekNo AND                                   -- when following week of previous day,
                           ((-1 ^ (pre.dowBit*2-1)) | (w.dowBit-1)) & pre.WeekDays > 0 -- days between this and previous weekdays, compared to current series
                          )) THEN w.RowID END AS StartRowID
        FROM WeeksCTE pre
        JOIN @Ranked w ON (w.RowID = pre.RowID + 1)
        ) w
) 
INSERT INTO @RankedRanges -- days sequence and starting point of each sequence
SELECT RowID, WeekDays, StartRowID
--SELECT *
FROM WeeksCTE
OPTION (MAXRECURSION 0)

--SELECT * FROM @RankedRanges

DECLARE @Ranges TABLE (RowNo int NOT NULL IDENTITY PRIMARY KEY, RowID int NOT NULL);

INSERT INTO @Ranges       -- @RankedRanges filtered only by start of each range, with numbered rows to easily find the end of each range
SELECT StartRowID
FROM @RankedRanges
WHERE StartRowID IS NOT NULL
ORDER BY 1

-- Final result putting everything together
SELECT rs.ContractID, rs.dt AS StartDT, re.dt AS EndDT, re.RowID-rs.RowID+1 AS DayCount,
       CASE WHEN rr.WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
       CASE WHEN rr.WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
       CASE WHEN rr.WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
       CASE WHEN rr.WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
       CASE WHEN rr.WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
       CASE WHEN rr.WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
       CASE WHEN rr.WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
FROM (
    SELECT r.RowID AS StartRowID, COALESCE(pos.RowID-1, (SELECT MAX(RowID) FROM @Ranked)) AS EndRowID
    FROM @Ranges r
    LEFT JOIN @Ranges pos ON (pos.RowNo = r.RowNo + 1)
    ) g
JOIN @Ranked rs ON (rs.RowID = g.StartRowID)
JOIN @Ranked re ON (re.RowID = g.EndRowID)
JOIN @RankedRanges rr ON (rr.RowID = re.RowID)

Another strategy

This one should be significantly faster than the previous one because it doesn't rely on the slow limited recursive CTE in SQL Server 2008, although it implements more or less the same strategy.

There is a WHILE loop (I couldn't devise a way to avoid it), but goes for a reduced number of iterations (the highest number of sequences (minus one) on any given contract).

It's a simple strategy, and could be used for sequences either shorter or longer than a week (replacing any occurrence of the constant 7 for any other number, and the dowBit calculated from MODULUS x of DayNo rather than DATEPART(wk)) and up to 32.

SET DATEFIRST 1 -- Make Monday weekday=1

-- Get the minimum information needed to calculate sequences
DECLARE @Days TABLE (ContractID int NOT NULL, dt date, DayNo int NOT NULL, dowBit int NOT NULL, PRIMARY KEY (ContractID, DayNo));
INSERT INTO @Days
SELECT ContractID, dt, CAST(CAST(dt AS datetime) AS int) AS DayNo, POWER(2, DATEPART(dw, dt)-1) AS dowBit
FROM @Src

DECLARE @RangeStartFirstPass TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo))

-- Calculate, from the above list, which days are not present in the previous 7
INSERT INTO @RangeStartFirstPass
SELECT r.ContractID, r.DayNo
FROM @Days r
LEFT JOIN @Days pr ON (pr.ContractID = r.ContractID AND pr.DayNo BETWEEN r.DayNo-7 AND r.DayNo-1) -- Last 7 days
GROUP BY r.ContractID, r.DayNo, r.dowBit
HAVING r.dowBit & COALESCE(SUM(pr.dowBit), 0) = 0

-- Update the previous list with all days that occur right after a missing day
INSERT INTO @RangeStartFirstPass
SELECT *
FROM (
    SELECT DISTINCT ContractID, (SELECT MIN(DayNo) FROM @Days WHERE ContractID = d.ContractID AND DayNo > d.DayNo + 7) AS DayNo
    FROM @Days d
    WHERE NOT EXISTS (SELECT 1 FROM @Days WHERE ContractID = d.ContractID AND DayNo = d.DayNo + 7)
    ) d
WHERE DayNo IS NOT NULL AND
      NOT EXISTS (SELECT 1 FROM @RangeStartFirstPass WHERE ContractID = d.ContractID AND DayNo = d.DayNo)

DECLARE @RangeStart TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo));

-- Fetch the first sequence for each contract
INSERT INTO @RangeStart
SELECT ContractID, MIN(DayNo)
FROM @RangeStartFirstPass
GROUP BY ContractID

-- Add to the list above the next sequence for each contract, until all are added
-- (ensure no sequence is added with less than 7 days)
WHILE @@ROWCOUNT > 0
  INSERT INTO @RangeStart
  SELECT f.ContractID, MIN(f.DayNo)
  FROM (SELECT ContractID, MAX(DayNo) AS DayNo FROM @RangeStart GROUP BY ContractID) s
  JOIN @RangeStartFirstPass f ON (f.ContractID = s.ContractID AND f.DayNo > s.DayNo + 7)
  GROUP BY f.ContractID

-- Summarise results
SELECT ContractID, StartDT, EndDT, DayCount,
       CASE WHEN WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
       CASE WHEN WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
       CASE WHEN WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
       CASE WHEN WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
       CASE WHEN WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
       CASE WHEN WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
       CASE WHEN WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
FROM (
    SELECT r.ContractID,
           MIN(d.dt) AS StartDT,
           MAX(d.dt) AS EndDT,
           COUNT(*) AS DayCount,
           SUM(DISTINCT d.dowBit) AS WeekDays
    FROM (SELECT *, COALESCE((SELECT MIN(DayNo) FROM @RangeStart WHERE ContractID = rs.ContractID AND DayNo > rs.DayNo), 999999) AS DayEnd FROM @RangeStart rs) r
    JOIN @Days d ON (d.ContractID = r.ContractID AND d.DayNo BETWEEN r.DayNo AND r.DayEnd-1)
    GROUP BY r.ContractID, r.DayNo
    ) d
ORDER BY ContractID, StartDT

Best Answer

Related Solutions

Sql-server – Calculate Total Visits

Query:

Output:

Output:

Data used with 2 different ids:

Sql-server – Group daily schedule into [Start date; End date] intervals with the list of week days

Another strategy

Related Question