I want to have the number of open requests for each month before the 1st of each month. I use ComboChart and my orange line is, at the moment, the average for the 6 past months, I want to change this data for the opened requests.
Exemple : for April, I have 2 new requests and 1 closed request and I want the total of the requests open before the April 1st.
The period is for 6 months before and the selected month. It will be a plus if you can use PIVOT 🙂
Here what I have so far
x
select top 1
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=7 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <7) )
) AS nbEnCours_1,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=6 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <6) )
) AS nbEnCours_2,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=5 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <5) )
) AS nbEnCours_3,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=4 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <4) )
) AS nbEnCours_4,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=3 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <3) )
) AS nbEnCours_5,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=2 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <2) )
) AS nbEnCours_6,
(
select count(d.id) AS nbEncours
FROM [diagrammes].[demandes] as d
WHERE DateDiff(month, d.dateEnvoi, GETDATE()) >=1 AND
( (d.statut=1 OR d.statut=2 OR d.statut=3 OR d.statut=4 OR d.statut=7) OR ( DateDiff(month, d.dateApprobation, GETDATE()) <1) )
) AS nbEnCours_7
The results is what I expect, but I think it can be improve. What I want is this
4 5 6 7 8 9 10
52 53 45 49 60 58 55
Advertisement
Answer
try converting each Select to a CASE statement that evaluates to 1 if the conditions are true and zero otherwise. Then SUM each case statement e.g. (pseudo-code, not actual SQL):
SELECT
SUM(CASE WHEN some logic = true THEN 1 ELSE 0) AS nbEnCours_1,
SUM(CASE WHEN some other logic = true THEN 1 ELSE 0) AS nbEnCours_2,
FROM TABLE;