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
|
CREATE TABLE dbo.cart_abonnements (id_utilisateur int primary key, date_validation datetime)
GO
INSERT INTO dbo.cart_abonnements VALUES (12, '20060101')
INSERT INTO dbo.cart_abonnements VALUES (24, '20060111')
INSERT INTO dbo.cart_abonnements VALUES (356, '20060201')
INSERT INTO dbo.cart_abonnements VALUES (1, '20060211')
INSERT INTO dbo.cart_abonnements VALUES (22, '20060211')
INSERT INTO dbo.cart_abonnements VALUES (156, '20060301')
INSERT INTO dbo.cart_abonnements VALUES (234, '20060311')
INSERT INTO dbo.cart_abonnements VALUES (56, '20060401')
INSERT INTO dbo.cart_abonnements VALUES (123, '20060411')
INSERT INTO dbo.cart_abonnements VALUES (2, '20060421')
INSERT INTO dbo.cart_abonnements VALUES (5, '20060411')
INSERT INTO dbo.cart_abonnements VALUES (7, '20060521')
INSERT INTO dbo.cart_abonnements VALUES (125, '20060501')
INSERT INTO dbo.cart_abonnements VALUES (21, '20060511')
INSERT INTO dbo.cart_abonnements VALUES (33, '20060601')
GO
SELECT YEAR(a1.date_validation) as Annee,
MONTH(a1.date_validation) as Mois,
COUNT(DISTINCT a2.id_utilisateur) as NombreCumule
FROM dbo.cart_abonnements a1
JOIN dbo.cart_abonnements a2 ON
CONVERT(char(6), a1.date_validation, 112) >=
CONVERT(char(6), a2.date_validation, 112)
GROUP BY YEAR(a1.date_validation), MONTH(a1.date_validation)
ORDER BY YEAR(a1.date_validation), MONTH(a1.date_validation)
GO
DROP TABLE dbo.cart_abonnements
GO |
Partager