SQL – How can I rewrite this request without many SELECT

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

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

Evolution of requests

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;

Advertisement

Answer