0

This query pulls data from a VistaDB and produces info on the number of courses started in each month of the year from people in different countries.

Select c.CountryName As Country,
  Count (case When Month( ch.CourseStarted ) = 1 Then 1 End) As Jan19,
  Count (case when Month(ch.CourseStarted  ) = 2 Then 1 End) as Feb19,
  Count (case When Month(ch.CourseStarted  ) = 3 Then 1 End) as Mar19,
  Count (case When Month(ch.CourseStarted  ) = 4 Then 1 End) as Apr19,
  Count (case When Month(ch.CourseStarted  ) = 5 Then 1 End) as May19,
  Count (case When Month(ch.CourseStarted  ) = 6 Then 1 End) as Jun19,
  Count (case When Month(ch.CourseStarted  ) = 7 Then 1 End) as Jul19,
  Count (case When Month(ch.CourseStarted  ) = 8 Then 1 End) as Aug19,
  Count (case When Month(ch.CourseStarted  ) = 9 Then 1 End) as Sep19,
  Count (case When Month(ch.CourseStarted  ) = 10 Then 1 End) as Oct19,
  Count (case When Month(ch.CourseStarted  ) = 11 Then 1 End)as Nov19,
  Count (case When Month(ch.CourseStarted  ) = 12 Then 1 End) as Dec19
From Country As c
  Inner Join CourseHistory As ch On c.Oid = ch.Country
Where (ch.CourseStarted >= '2019-01-01' And
       ch.CourseStarted <= '2019-12-31')
Group By c.CountryName
Order by c.CountryName;

My question is would it be possible to make this semi-dynamic so that if I were to make the final date in the where clause '2022-12-31' I could get a rafft of colums for each month of each year?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Dom Sinclair
  • 2,458
  • 1
  • 30
  • 47
  • Add `Year( ch.CourseStarted)` to SELECT clause and GROUP BY clause, remove WHERE clause, and see what happens. – jarlh Sep 29 '22 at 10:42
  • @jarlh That's close to what I want thank you but its producing a row for each individual course take over the period, Adding Distinct does not help in this case unfortunately. – Dom Sinclair Sep 29 '22 at 10:59

0 Answers0