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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
| 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-05-01', 10;
INSERT INTO @Subscriptions SELECT 3, '2010-06-01', 10;
INSERT INTO @Subscriptions SELECT 3, '2010-07-01', 10;
WITH
ACTIVITY_FIRST_MONTH AS
(
SELECT S.ID
, AFM.StartMonth
, SUM(S.Actions) AS TotalActions
FROM (
SELECT ID
, MIN(MONTH(DateActivity)) AS StartMonth
FROM @Subscriptions
GROUP BY ID
) AS AFM
INNER JOIN @Subscriptions AS S
ON S.ID = AFM.ID
AND MONTH(S.DateActivity) = AFM.StartMonth
GROUP BY S.ID , AFM.StartMonth
)
, ACTIVITY_LAST_MONTH AS
(
SELECT S.ID
, AFM.LastMonth
, SUM(S.Actions) AS TotalActions
FROM (
SELECT ID
, MAX(MONTH(DateActivity)) AS LastMonth
FROM @Subscriptions
GROUP BY ID
) AS AFM
INNER JOIN @Subscriptions AS S
ON S.ID = AFM.ID
AND MONTH(S.DateActivity) = AFM.LastMonth
GROUP BY S.ID , AFM.LastMonth
)
SELECT L.ID
, F.StartMonth
, F.TotalActions
, L.TotalActions
, L.LastMonth
FROM ACTIVITY_LAST_MONTH AS L
INNER JOIN ACTIVITY_FIRST_MONTH AS F
ON L.ID = F.ID; |
Partager