1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
|
DECLARE @Subscriptions TABLE (ID Int, DateActivity Date, Actions Int)
INSERT INTO @Subscriptions SELECT 1, '2010-01-01', 10
INSERT INTO @Subscriptions SELECT 1, '2010-01-02', 10
INSERT INTO @Subscriptions SELECT 1, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-01-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-01-02', 10
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-03-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
SELECT S.ID, DATEDIFF(month, MinActivity, DateActivity) + 1 As MonthNo, SUM(Actions) As QtyActions
FROM @Subscriptions S
JOIN
(
SELECT ID, MIN(DateActivity) As MinActivity
FROM @Subscriptions
GROUP BY ID
) Ranges ON S.ID = Ranges.ID
GROUP BY S.ID, DATEDIFF(month, MinActivity, DateActivity) + 1
ORDER BY 1, 2 |
Partager