Discussion :

[LouFeq]

Voici mon code qui me renvoie le résultat attendu:

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
SELECT C.Name, CC.*
FROM 
	(	
	SELECT I.CustNum,
		   SUM(CASE WHEN I.InvoiceDate = cast (GETDATE() as DATE) THEN I.InvoiceAmt END) as Aujourdhui, 
		   SUM(CASE WHEN (I.InvoiceDate <= cast (GETDATE() - 1 as DATE) AND I.InvoiceDate > cast (GETDATE() - 7 as DATE)) THEN I.InvoiceAmt END) as Jours_1_7,
		   SUM(CASE WHEN (I.InvoiceDate <= cast (GETDATE() - 7 as DATE) AND I.InvoiceDate > cast (GETDATE() - 14 as DATE)) THEN I.InvoiceAmt END) as Jours_7_14,
		   SUM(CASE WHEN (I.InvoiceDate <= cast (GETDATE() - 14 as DATE) AND I.InvoiceDate > cast (GETDATE() - 30 as DATE)) THEN I.InvoiceAmt END) as Jours_14_30,
		   SUM(CASE WHEN (I.InvoiceDate <= cast (GETDATE() - 30 as DATE) AND I.InvoiceDate > cast (GETDATE() - 60 as DATE)) THEN I.InvoiceAmt END) as Jours_30_60,
		   SUM(CASE WHEN I.InvoiceDate <= cast (GETDATE() - 60 as DATE)  THEN I.InvoiceAmt END) as Jours_60	   
	FROM InvcHead I	
	WHERE OpenInvoice = 1
	GROUP BY I.CustNum
	) CC
	INNER JOIN Customer C
	ON C.CustNum = CC.CustNum
ORDER BY C.Name

Autrement dit, j'effectue une somme sur plusieurs intervalles de dates. Ma question: J'aimerais savoir s'il existe une façon plus élégante d'obtenir les résultats? En utilisant une CTE?

Merci à l'avance

[SQLpro]

Respectez la charte de postage : http://www.developpez.net/forums/d96...vement-poster/

Sans cela on ne peut vous aider !

A +

[Sergejack]

Vous pourriez isoler vos calcul de date.

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
 
SELECT
 SUM(CASE WHEN I.InvoiceDate = D0 THEN I.InvoiceAmt END) AS Aujourdhui
, SUM(CASE WHEN (I.InvoiceDate < D0 AND I.InvoiceDate > D7 THEN I.InvoiceAmt END) AS Jours_1_7,
 [...]
FROM ...
CROSS JOIN (
 VALUES(
  DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 0, 0)
 ,  DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 7, 0)
 ,  DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 14, 0)
 )
) AS Dates (
 D0
 , D7
 , D14
)