This first query creates different Start Date and End Date ranges with no overlaps.
Note:
- Your sample(
id=0
) is mixed with a sample from Ypercube (id=1
)
- This solution may not scale well with huge amount of data for each id or huge number of id. This has the advantage of not requiring a number table. With large dataset, a number table will very likely give better performances.
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')
Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month.
USE Masscomm_XT;
WITH C0 AS (
SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
FROM dbo.reptview_LDUsageTrend
)
SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (Minutes) AS MinuteTotal
FROM C0
GROUP BY C0.CustomerName, C0.PostingYear, C0.PostingMonth
;
Best Answer
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c2d6d92223f6516d78550a021cd5c3ce
The query assumes that all periods belongs the same year. Of course it can be simplified. The period length from '2021-04-20' to '2021-04-30' (inclusive) is 11 days, not 10.
This affects only
cte1
(generate the calendar based not on table data but on needed period dates) and, if you want to filter some month partially (not from first day till the last day of month) oncte2
. Main query will be filtered automatically due toON
clause expression.