Annonce: TSQL Challenges

**agemis31** · 08/05/2009, 14h54

Bonjour à tous,

Je vous signale, pour ceux qui veulent s'amuser tout en progressant en T-SQL, que vous pouvez trouver des défis intéressants sur TSQL Challenges

Le projet est à l'initiative de Jacob Sebastian, MVP SQL Server.

Bon amusement

@+

**SQLpro** · 11/05/2009, 14h42

Aller un petite solution au challenge 6 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
;
WITH 
T_DAY AS
(
SELECT DISTINCT DATEPART(weekday, VisitDate) AS WD, 
       UPPER(SUBSTRING(DATENAME(weekday, VisitDate), 1, 3)) AS WDN
FROM   @T
),
T_CUMUL AS -- summing for the finest date aggregate (day of week)
(
SELECT YEAR(VisitDate) AS Y, DATEPART(week, VisitDate) AS W, 
       DAY(VisitDate) AS D, MONTH(VisitDate) AS M,
       DATEPART(weekday, VisitDate) AS WD, Page, SUM(NbVisitors) AS CUMUL
FROM   @T
GROUP  BY YEAR(VisitDate), DATEPART(week, VisitDate), 
          DATEPART(weekday, VisitDate), DAY(VisitDate), MONTH(VisitDate), Page
),
T_PAGE AS -- retrieving all pages
(
SELECT DISTINCT Page
 FROM   @T
),
T_CROSS AS -- crossing pages and date aggregate
(
SELECT P.Page, Y, W, WD, D, M
FROM   T_PAGE AS P
       CROSS JOIN (SELECT DISTINCT Y, W, WD, D, M FROM T_CUMUL) AS C
),
T_ALL AS -- calculating all entries of weeks/month/year, transforming nulls in 0
(
SELECT M.*, COALESCE((SELECT CUMUL 
                      FROM   T_CUMUL AS C
                      WHERE  M.Page = C.Page
                        AND  M.Y = C.Y
                        AND  M.W = C.W
                        AND  M.WD = C.WD), 0) AS TOTAL
FROM   T_CROSS AS M
),
T_FLAT AS -- flatening the pages
(
SELECT Y, W, WD, D, M, 
       SUM(HOME) AS HOME, SUM(CONTACT) AS CONTACT, SUM(PRODUCTS) AS PRODUCTS
FROM   (SELECT Y, W, WD, D, M, TOTAL AS HOME, 0 AS CONTACT, 0 AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Home'
        UNION ALL
        SELECT Y, W, WD, D, M, 0 AS HOME, TOTAL AS CONTACT, 0 AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Contact'
        UNION ALL
        SELECT Y, W, WD, D, M, 0 AS HOME, 0 AS CONTACT, TOTAL AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Products') AS T
GROUP BY Y, W, WD, D, M
),
T_LEVEL AS -- introducing the summating levels
(
SELECT Y, W, WD, D, M, 0 AS LEVEL, HOME, CONTACT, PRODUCTS
FROM   T_FLAT
UNION ALL
SELECT Y, W, NULL, NULL, NULL, 1, SUM(HOME), SUM(CONTACT), SUM(PRODUCTS)
FROM   T_FLAT
GROUP  BY Y, W
UNION ALL
SELECT Y, NULL, NULL, NULL, NULL, 2, SUM(HOME), SUM(CONTACT), SUM(PRODUCTS)
FROM   T_FLAT
GROUP  BY Y
)
-- comectics purpose : PERIOD_ID, PERIOD
SELECT CAST(Y AS CHAR(4)) 
        + CASE WHEN W IS NULL THEN 'T' ELSE '' END
        + CASE WHEN W < 10 THEN '0' ELSE '' END 
        + COALESCE(CAST(W AS VARCHAR(2)), '')
        + CASE WHEN L.WD IS NULL AND W IS NOT NULL 
                  THEN 'T' ELSE COALESCE(CAST(L.WD AS CHAR(1)), '') 
          END AS PERIOD_ID,
       LEVEL,
       CASE WHEN W IS NULL 
               THEN 'TOTAL YEAR ' + CAST(Y AS CHAR(9))
            WHEN L.WD IS NULL AND W IS NOT NULL 
               THEN 'TOTAL WEEK ' + 
                  CASE WHEN W < 10 
                     THEN '0' ELSE '' 
                  END + COALESCE(CAST(W AS VARCHAR(2)), '') 
            ELSE '   ' + WDN + '  ' + CAST(D AS VARCHAR(2)) 
                 + '/' + CAST(M AS VARCHAR(2))
       END AS PERIOD,
       HOME, CONTACT, PRODUCTS
FROM    T_LEVEL AS L
        LEFT OUTER JOIN T_DAY AS D
              ON L.WD = D.WD
ORDER BY PERIOD_ID;
GO

A +

**elsuket** · 11/05/2009, 19h17

Bonjour,

Une autre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
SET DATEFIRST 1
SET DATEFORMAT YMD
SET NOCOUNT ON
SET LANGUAGE us_english
GO
 
DECLARE @T TABLE (ID INT IDENTITY(1,1), VisitDate DATETIME, Page NVARCHAR(15),NbVisitors INT) 
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Home',3)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Home',4)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Home',33)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-03','Home',2)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Contact',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Contact',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Contact',35)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-30','Contact',13)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Products',8)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Products',12)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Products',16)
 
;WITH
	YEAR_TOTAL AS
	(
		SELECT CAST(VisitYear AS CHAR(4)) + 'T' AS PeriodID,
				2 AS LEVEL,
				53 AS VisitWeek,
				'TOTAL YEAR ' + CAST(VisitYear AS CHAR(4)) AS Period,
				Home,
				Contact,
				Products		
		FROM
		(
			SELECT YEAR(VisitDate) AS VisitYear,
					53 AS VisitWeek,
					Page,
					NbVisitors
			FROM @T
		) AS BRUT
		PIVOT
		(
			SUM(NbVisitors)
			FOR Page IN (Home, Contact, Products)
		) AS PVT
	),
	WEEK_TOTAL AS
	(
		SELECT CAST(VisitYear AS CHAR(4)) + CAST(VisitWeek AS VARCHAR(2)) + 'T' AS PeriodID,
				1 AS LEVEL,
				VisitWeek,
				'TOTAL WEEK ' + CAST(VisitWeek AS VARCHAR(2)) AS Period,
				Home,
				Contact,
				Products
		FROM
		(
			SELECT YEAR(VisitDate) AS VisitYear,
					DATEPART(week, VisitDate) AS VisitWeek,
					Page,
					NbVisitors
			FROM @T
		) AS BRUT
		PIVOT
		(
			SUM(NbVisitors)
			FOR Page IN (Home, Contact, Products)
		) AS PVT
	),
	DAY_TOTAL AS
	(
		SELECT CAST(YEAR(PeriodID) AS CHAR(4)) + CAST(DATEPART(week, PeriodID) AS VARCHAR(2)) + CAST(DATEPART(weekday, PeriodID) AS CHAR(1)) AS PeriodID,
				LEVEL,
				CAST(DATEPART(week, PeriodID) AS VARCHAR(2)) AS VisitWeek,
				Period + ' ' + CAST(DAY(Date) AS VARCHAR) + '/' + CAST(MONTH(Date) AS VARCHAR) AS Period,
				Home,
				Contact,
				Products
		FROM
		(
			SELECT PeriodID,
					0 AS LEVEL,
					LEFT(UPPER(DATENAME(weekday, PeriodID)), 3) AS Period,
					CAST(PeriodID AS DATETIME) AS Date,
					ISNULL(Home, 0) AS Home,
					ISNULL(Contact, 0) AS Contact,
					ISNULL(Products, 0) AS Products
			FROM
			(
				SELECT PeriodID, Home, Contact, Products
				FROM
				(
					SELECT VisitDate AS PeriodID,
							Page,
							NbVisitors
					FROM @T
				) AS BRUT
				PIVOT
				(
					SUM(NbVisitors)
					FOR Page IN (Home, Contact, Products)
				) AS PVT
			) AS ROTATE
		) AS GET_DAY_NAMES
	)
SELECT PeriodID, Level, Period, Home, Contact, Products
FROM
(
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM YEAR_TOTAL
	UNION
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM WEEK_TOTAL
	UNION
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM DAY_TOTAL
) AS FINAL

@++

**agemis31** · 11/05/2009, 19h30

Oups !

Pas mal

C'est marrant de voir l'approche de chacun.

Ca doit en motiver certains non ?

Comme les challenges actuels sont individuels. Merci d'envoyer vos réponses à Jacob Sebastian

Si ca vous intéresse, on peut faire une review après la publication des solutions.

@+

**Chtulus** · 11/05/2009, 22h51

Bonsoir tout le monde,

Bon je résume mon sentiment en ce moment:

=

Faut que je me mettes au boulot !

**lmalta** · 12/05/2009, 10h04

Bonjour,

Arrhh WITH ne fonctionne pas sous SQL2000 je n'ai donc pas pu tester les solutions proposées

Ma solution toute bête
trois niveaux demadés donc trois union
la colonne PERIOD_ID suffit à trier l'ensemble :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
select PERIOD_ID,min(LEVEL) LEVEL ,min(PERIOD) PERIOD 
,sum(case Page when 'Home' then sumNbVisitors else 0 end) as HOME
,sum(case Page when 'Contact' then sumNbVisitors else 0 end) as CONTACT
,sum(case Page when 'Products' then sumNbVisitors else 0 end )as PRODUCTS
from 
(
select Annee+Semaine+Jour as PERIOD_ID,LEVEL ,PERIOD,Page , sum(sumNbVisitors)  sumNbVisitors from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                   'T' as Semaine,
                   ' ' as Jour,
                        2 AS LEVEL,
                        'TOTAL YEAR '+convert(char(4),YEAR(VisitDate)) AS PERIOD,
                        Page,
                        sum(NbVisitors) sumNbVisitors
                        from @T 
                    group by convert(char(4),YEAR(VisitDate))
                    ,VisitDate,Page
            ) y group by Annee+Semaine+Jour,LEVEL ,PERIOD,Page
union
select Annee+Semaine+Jour as PERIOD_ID,
LEVEL ,
PERIOD,Page ,  sum(sumNbVisitors)  sumNbVisitors  from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                   convert(varchar(2),DATEPART(wk,VisitDate)) as Semaine,
                   'T' as Jour,
                        1 AS LEVEL,
                        'TOTAL WEEK '+convert(varchar(2),DATEPART(wk,VisitDate)) AS PERIOD,
                        Page,
                        sum(NbVisitors) sumNbVisitors
                        from @T 
                    group by convert(char(4),YEAR(VisitDate)),convert(varchar(2),DATEPART(wk,VisitDate))
                    ,VisitDate,Page
            ) w group by Annee+Semaine+Jour,LEVEL ,PERIOD,Page
union
            select Annee+Semaine+Jour as PERIOD_ID,LEVEL ,PERIOD,Page , sumNbVisitors  from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                               convert(varchar(2),DATEPART(wk,VisitDate)) as Semaine,
                               convert(char(1),DATEPART(dw,VisitDate)) as Jour,
                               0 AS LEVEL,
                                    convert(char(3), Upper (DATENAME(month, VisitDate))) 
                                   +' '+convert(varchar(2),DAY(VisitDate))+'/'+convert(varchar(2),MONTH(VisitDate)) as PERIOD,
                                   Page,
                                   sum(NbVisitors) sumNbVisitors
                                   from @T group by convert(char(4),YEAR(VisitDate)),convert(varchar(2),DATEPART(wk,VisitDate)),convert(char(1),DATEPART(dw,VisitDate))
                              ,VisitDate,Page
                        ) d
) a
group by PERIOD_ID
order by 1

A+
Louis

**agemis31** · 12/05/2009, 13h08

Merci Imalta !

Très joli et sans CTE

@+

**mikedavem** · 12/05/2009, 20h19

Bon aller je mets le mien , j'ai eu un peu de temps à tuer, j'ai vu de la lumière donc ....

Sans trop avoir regardé , je crois bien que j'ai procédé de la meme manière que Elsuket (CTE et PIVOT)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
SET DATEFIRST 1
SET DATEFORMAT YMD
SET NOCOUNT ON
SET LANGUAGE us_english
GO
 
DECLARE @T TABLE (ID INT IDENTITY(1,1), VisitDate DATETIME, Page NVARCHAR(15),NbVisitors INT) 
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Home',3)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Home',4)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Home',33)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-03','Home',2)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Contact',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Contact',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Contact',35)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-30','Contact',13)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Products',8)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Products',12)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Products',16);
 
WITH TOTAL_PER_DAY
AS
(
	SELECT 
		CAST(YEAR(VisitDate) AS CHAR(4)) + 
		 CAST(DATEPART(wk,VisitDate) AS VARCHAR(2)) + 
		  CAST(DATEPART(dw,VisitDate) AS VARCHAR(2)) AS PERIOD_ID,
		'0' AS LEVEL,
		LEFT(UPPER(DATENAME(weekday,VisitDate)),3) + ' ' + 
		 CAST(DATEPART(dd,VisitDate) AS VARCHAR(2)) + '/' + 
		  CAST(DATEPART(mm,VisitDate) AS VARCHAR(2)) AS PERIOD,
		ISNULL([Home],0) AS HOME,
		ISNULL([Contact],0) AS CONTACT,
		ISNULL([Products],0) AS PRODUCTS
	FROM 
	(
		SELECT 
			VisitDate,
			Page,
			NbVisitors
		FROM @T
	) AS T_DAY
	PIVOT
	(
		SUM(NbVisitors)
		FOR Page IN (Home,Contact,Products)
	) AS PVT_DAY
),
TOTAL_PER_WEEK
AS 
(
SELECT 
		CAST(year_week AS CHAR(4)) + CAST(week_ AS VARCHAR(2)) + 'T' AS PERIOD_ID,
		'1' AS LEVEL,
		'TOTAL WEEK ' + CAST(week_ AS VARCHAR(2)) AS PERIOD,
		ISNULL([Home],0) AS HOME,
		ISNULL([Contact],0) AS CONTACT,
		ISNULL([Products],0) AS PRODUCTS
	FROM 
	(
		SELECT 
			YEAR(VisitDate) AS year_week,
			DATEPART(ww,VisitDate) AS week_,
			Page,
			NbVisitors
		FROM @T
	) AS T_WEEK
	PIVOT
	(
		SUM(NbVisitors)
		FOR Page IN (Home,Contact,Products)
	) AS PVT_WEEK
),
TOTAL_PER_YEAR
AS
(
	SELECT 
			CAST(yearVisit AS CHAR(4)) + 'T' AS PERIOD_ID,
			'2' AS LEVEL,
			'TOTAL YEAR ' + CAST(yearVisit AS CHAR(4)) AS PERIOD,
			ISNULL([Home],0) AS HOME,
			ISNULL([Contact],0) AS CONTACT,
			ISNULL([Products],0) AS PRODUCTS
		FROM 
		(
			SELECT 
				YEAR(VisitDate) AS yearVisit, 
				Page,
				NbVisitors
			FROM @T
		) AS T_WEEK
		PIVOT
		(
			SUM(NbVisitors)
			FOR Page IN (Home,Contact,Products)
		) AS PVT_YEAR
)
SELECT PERIOD_ID, LEVEL, PERIOD,  HOME, CONTACT, PRODUCTS FROM TOTAL_PER_DAY
UNION ALL
SELECT PERIOD_ID, LEVEL, PERIOD,  HOME, CONTACT, PRODUCTS  FROM TOTAL_PER_WEEK
UNION ALL
SELECT PERIOD_ID, LEVEL, PERIOD,  HOME, CONTACT, PRODUCTS  FROM TOTAL_PER_YEAR
ORDER BY PERIOD_ID;

++

**agemis31** · 12/05/2009, 22h20

Alors la !

Il y a de la synergie dans l'air entre mikedavem et elsuket

j'ai eu un peu de temps à tuer, j'ai vu de la lumière donc

Merci mikedavem, avoue que c'est une occupation de dingue, mais bon tant qu'on s'amuse

@+

**elsuket** · 12/05/2009, 22h25

En tout cas c'est vraiment intéressant de voir les différentes façons de réfléchir de chacun.
Bravo à lmalta qui n'a pas utilisé de CTE

@++

**mikedavem** · 12/05/2009, 22h43

Envoyé par agemis31

Alors la !

Il y a de la synergie dans l'air entre mikedavem et elsuket

Merci mikedavem, avoue que c'est une occupation de dingue, mais bon tant qu'on s'amuse

@+

J'avoue que je me suis vraiment intéressé aux CTE que lorsque j'ai vu Elsuket

s'en servir sur le forum ... Il est vrai que les CTE c quelque chose de puissant ...

Oui c'est vrai que ca occupe .. Certains diront ils sont fous ces informaticiens à s'amuser a faire du code ... y'a tant d'autres choses à faire ..

**jero44** · 13/05/2009, 12h49

Bonjour,
a mon tour de vous proposer une solution. J'utilise un pivot pour transformer la structure puis je confie à ROLLUP la génération des totaux intermédiaire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
select
	convert(char(4),max(year))+
	case (grouping(visitDate)+grouping(week))
		when 0 then convert(char(2),week)+convert(char,datepart(dw,VisitDate))
		when 1 then convert(char(2),week)+'T'
	    when 2 then 'T'
	end as PERIOD_ID,
	grouping(visitDate)+grouping(week) as LEVEL,
	convert(char(15),case (grouping(visitDate)+grouping(week))
		when 0 then  upper(substring(datename(dw, VisitDate),1,3)) +' '+datename(day, VisitDate)+'/'+convert(char,datepart(mm, VisitDate)) 
		when 1 then 'TOTAL WEEK '+convert(char(2),week)
	    when 2 then 'TOTAL YEAR '+convert(char(4), max(year))
	end) as PERIOD,
	sum(home) as HOME , sum(contact) as CONTACT, sum(products) as PRODUCTS
from (
select	VisitDate,
		datepart (yyyy, visitDate) as year,
		datepart(ww,visitDate) as week,
		sum(isnull([Home],0)) as home,
		sum(isnull([Contact],0)) as Contact,
		sum(isnull([Products],0)) as Products
from @t pivot (sum(nbvisitors) for Page in([Home],[Contact],[Products]) ) as pvt
group by visitDate) as t
group by week,visitDate with rollup;

**agemis31** · 13/05/2009, 13h06

Bonjour jero44,

Merci, c'est une très belle solution compacte et lisible

Qui permet de découvrir GROUP BY WITH ROLLUP, parfaitement adapté ici.

@+

**SQLpro** · 13/05/2009, 17h06

Je suis pas pour le PIVOT qui est une grosse merde et n'existe pas en SQL. C'est spécifique à SQL Server.
En revanche bravo pour l'idée du ROLUP et surtout d'avoir pris la syntaxe normative et non celle spécifique à SQL Server, Syntaxe normative introduite récemment depuis 2008.

Maintenant il faut voir sur un fort volume laquelle sera la plus performante.
A mon avis la mienne sera la plus dégueu... vue que je fait un produit cartésien !!!

A +

**elsuket** · 13/05/2009, 19h40

J'avais pensé faire avec ROLLUP, mais je ne sais pas j'ai eu peur de ne pas arriver à mes fins

Bravo donc à Jero44 pour cette audace

Moi non plus je n'aime pas PIVOT mais comme je n'avais jamais eu l'occasion de l'utiliser, j'en ai profité ce coup-ci.
Après je ne sais pas ce que donne PIVOT en terme de performances, mais ça ne doit pas être terrible non plus

@++

**mikedavem** · 13/05/2009, 19h54

Ah cet après midi j'avais commencé à regarder avec grouping mais sans résultat.
J'en étais resté à étudier GROUPING WITH CUBE ....

En tout cas bravo jero44

++

**jero44** · 14/05/2009, 09h32

Merci de ce retour

**agemis31** · 14/05/2009, 13h11

Bonjour,

Ca va être difficile de passer après jero44 !

Quelqu'un d'autre ?

@+

**lmalta** · 15/05/2009, 11h43

bravo à jero44,

cela m'a permis de m'interresser à ROLLUP (sympas l'idée) pour simplifier et donc clarifier un de mes cas au taf

Le résultat obtenu par jero44 corresponds exactement à celui attendu avec les données à dispo et donc réponds parfaitement à l'énoncé initial.
Par contre si on ajoute des données en 2008 par exemple on perds la rupture YEAR 2008 et la somme de 2008 est ajouter sur 2009

du au rupture du rollup

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GROUP BY week,VisitDate WITH rollup;

Ci-dessous le nouveau jeu de test

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
DECLARE @T TABLE (ID INT IDENTITY(1,1), VisitDate DATETIME, Page NVARCHAR(15),NbVisitors INT) 
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2008-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2008-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2008-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2008-03-27','Home',3)
 
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Home',3)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Home',4)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Home',33)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-03','Home',2)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Contact',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Contact',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Contact',35)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-30','Contact',13)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Products',8)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Products',12)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Products',16)

Je me suis donc permis d'adapter (désolé) pour gérer cette rupture

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
SELECT
	isnull(convert(char(4),max(years))+
	case (grouping(years)+grouping(week)+grouping(VisitDate))
		when 0 then convert(char(2),week)+convert(char,datepart(dw,VisitDate))
		when 1 then convert(char(2),week)+'T'
	    when 2 then 'T'
 
	end,'') AS PERIOD_ID,
	grouping(years)+grouping(week)+grouping(VisitDate) AS LEVEL,
	convert(char(15),case (grouping(years)+grouping(week)+grouping(VisitDate))
		when 0 then  upper(substring(datename(dw, VisitDate),1,3)) +' '+datename(day, VisitDate)+'/'+convert(char,datepart(mm, VisitDate)) 
		when 1 then 'TOTAL WEEK '+convert(char(2),week)
	    when 2 then 'TOTAL YEAR '+convert(char(4), max(years))
        else 'TOTAL GLOBAL'
	end) AS PERIOD,
	sum(home) AS HOME , sum(contact) AS CONTACT, sum(products) AS PRODUCTS
FROM (
SELECT	VisitDate,
		datepart (yyyy, visitDate) AS years,
		datepart(ww,visitDate) AS week,
		sum(isnull([Home],0)) AS home,
		sum(isnull([Contact],0)) AS Contact,
		sum(isnull([Products],0)) AS Products
FROM @t pivot (sum(nbvisitors) FOR Page IN([Home],[Contact],[Products]) ) AS pvt
GROUP BY visitDate) AS t
GROUP BY years,week,VisitDate WITH rollup;

Et là je suis automatiquement confronté à un nouveau troisième niveau (et donc je ne réponds plus à l'objectif initial) correspondant au total général.
Je me retrouve dans l'impasse : comment peux t'on enlever ce dernier niveau ?bien sûr directement depuis la gestion rollup et donc sans passer par encore une sur requete avec un where sur 'TOTAL GLOBAL'
(Je sais c'est bête à partir d'un petit jeu, je reviens avec un problème)

@+

**agemis31** · 15/05/2009, 18h28

Bonsoir Imalta,

Je ne suis pas sur d'avoir compris ta question.

En ajoutant un regroupement sur l'année et l'année dans PERIOD_ID, sinon ca n'a plus beaucoup de sens, j'ai essayé ceci qui à l'air de marcher avec ton jeu de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT
	convert(char(4),max(year))+
	case (grouping(year)+grouping(visitDate)+grouping(week))
		when 0 then convert(char(2),week) +convert(char,datepart(dw,VisitDate))
		when 1 then convert(char(2),week)+'T'
	    when 2 then 'T'
	end AS PERIOD_ID,
	grouping(year)+grouping(visitDate)+grouping(week) AS LEVEL,
	convert(char(15),case (grouping(year)+grouping(visitDate)+grouping(week))
		when 0 then  convert(char(4), max(year)) + ' ' + upper(substring(datename(dw, VisitDate),1,3)) +' '+datename(day, VisitDate)+'/'+convert(char,datepart(mm, VisitDate)) 
		when 1 then  convert(char(4), max(year)) + ' ' + 'TOTAL WEEK '+convert(char(2),week)
	    when 2 then 'TOTAL YEAR '+convert(char(4), max(year))
	end) AS PERIOD,
	sum(home) AS HOME , sum(contact) AS CONTACT, sum(products) AS PRODUCTS
FROM (
SELECT	VisitDate,
		datepart (yyyy, visitDate) AS year,
		datepart(ww,visitDate) AS week,
		sum(isnull([Home],0)) AS home,
		sum(isnull([Contact],0)) AS Contact,
		sum(isnull([Products],0)) AS Products
FROM @t pivot (sum(nbvisitors) FOR Page IN([Home],[Contact],[Products]) ) AS pvt
GROUP BY visitDate) AS t
GROUP BY year,week,visitDate WITH rollup
HAVING convert(char(4),max(year))+
	case (grouping(year)+grouping(visitDate)+grouping(week))
		when 0 then convert(char(2),week) +convert(char,datepart(dw,VisitDate))
		when 1 then convert(char(2),week)+'T'
	    when 2 then 'T'
	end IS NOT NULL

@+

Annonce: TSQL Challenges

Développement SQL Server

Discussions similaires

Partager

Partager