CODEWITHSUNDEEP
sql-server
Hubert Solecki
Hubert Solecki

Reputation: 2761

Get data for each month even if it's null in sql server

In my context, customers are making request and that request are saved in database. For each month, i would like to count the number of requests for each month, so I've tried that :

Select COUNT(EveLngId) as NbreIncidentPrisEnDirect
FROM T_Evenement as TE inner join T_Historique on HisLngEveId  = EveLngId, T_Param as TPA
WHERE EveLngDosId = 1062
    And EveLngBibId = 268
    And HisLngBibId = 267
    And ParStrIndex = 'RES'
    And TE.EveLngResponsableId = TPA.ParLngId
    And EveDatRedaction = EveDatRealisation
    And year(EveDatRedaction) = '2013'
    group by MONTH(EveDatRedaction)

And I get that result :

enter image description here

So as you see, for that year (2013) I don't have the values for each month, Why ? Because for some of them, the value is 0. There is a way to display the value for each month even if the value is 0 ?

Upvotes: 0

Views: 1385

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Assuming that you have some data for each month (although not matching the where clause), you can use conditional aggregation:

Select sum(case when EveLngDosId = 106 And EveLngBibId = 268 And HisLngBibId = 267
                     And ParStrIndex = 'RES' And 
                     And EveDatRedaction = EveDatRealisation
                then 1 else 0
           end) as NbreIncidentPrisEnDirect
FROM T_Evenement as TE inner join
     T_Historique
     on HisLngEveId = EveLngId join
     T_Param as TPA
     on TE.EveLngResponsableId = TPA.ParLngId
WHERE year(EveDatRedaction) = '2013'
group by MONTH(EveDatRedaction);

The disadvantage is that this will not use indexes for the where clause.

Upvotes: 0

Kermit
Kermit

Reputation: 34055

The best solution would be to create a calendar table and JOIN your data. Otherwise, you will need to create a table:

SELECT 1 AS month, 2013 AS year
UNION ALL
SELECT 2, 2013
UNION ALL
SELECT 3, 2013
UNION ALL
SELECT 4, 2013
...

and JOIN it with your query.

Upvotes: 2

Related Questions