title: SQL - How can I rewrite this request without many SELECT
body: <p>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.</p>
  <p>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.</p>
  <p>The period is for 6 months before and the selected month.
  It will be a plus if you can use PIVOT :-)</p>
  <p>Here what I have so far</p>
  <pre><code>select top 1
  (
      select count(d.id) AS nbEncours 
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;7) )
  ) AS nbEnCours_1,
  (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;6) )
  ) AS nbEnCours_2,
  (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;5) )
  ) AS nbEnCours_3,
  (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;4) )
  ) AS nbEnCours_4,
  (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;3) )
  ) AS nbEnCours_5,
  (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;2) )
  ) AS nbEnCours_6,
      (
      select count(d.id) AS nbEncours
      FROM [diagrammes].[demandes] as d
      WHERE DateDiff(month, d.dateEnvoi, GETDATE()) &gt;=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()) &lt;1) )
  ) AS nbEnCours_7
  </code></pre>
  <p>The results is what I expect, but I think it can be improve. What I want is this</p>
  <pre><code>  4     5     6     7     8     9     10
   52     53   45    49    60    58     55
  </code></pre>
  <p><a href="https://i.stack.imgur.com/rV2Iu.png" rel="nofollow noreferrer">Evolution of requests</a></p>