SQL Query Month / Year

Question

I have a query which calculate values per year/months from table "mesure" like this

select Annee, Mois
from 
  (
    select  annee, Mois
        --  computing her...
      round(cast(SUM(nbDates) * 100 as float) / sum(NbDatesTheoriques),2) as    DispoBrute ie....
    from (
            select          
            DATEPART(YEAR, DateHeureMesure) as annee, 
            DATEPART(month, dateheuremesure) as Mois
            --TypeMesure,HauteurMesure, count(dateheuremesure) as nbDates,
            --SUM(CASE WHEN ValideeMesure = 2 THEN 1 ELSE 0 END) AS NbDatesValides
            from mesure                     
            where Id = 378                  
            group by DATEPART(YEAR, DateHeureMesure), DATEPART(month, dateheuremesure) 

        ) req
    )   req2
group by annee, mois--, mo.MonthNumber
order by annee, mois --min(datedebut)

I simplify the query (remove part of subquery req2 / req...) and the result is :

Year   Month   DispoBrute 
2013    8         156
2013    9         1254
2013    10        121
2013    11        2121
2013    12        4500
2014    1         155 
2014    2         200
2014    3 
2014    4
2014    5

Ok but how can i have all the months on the current year like this ?

Year   Month     DispoBrute
2013    1         NULL
2013    2         NULL
2013    3         NULL
2013    4         NULL
2013    5         NULL
2013    6         NULL
2013    7         NULL
2013    8         156
2013    9         1254
2013    10        121
2013    11 ... ...2121
2013    12 ....    ie
2014    1
2014    2
2014    3
2014    4
2014    5

Thanks a lot !

Use a tally table to `right join` (or `left join` if you start from the tally table) with, the tally table will only have numbers 1-12 (for the months). You can join on month number. — HoneyBadger, Apr 22 '16 at 13:41
Why float? Numeric (7,2) would probably be a better choice as it is an exact numeric. floats are approximate values and not every value can be stored. — Sean Lange, Apr 22 '16 at 13:58
Possible duplicate of [How do I get values for all months in T-SQL](http://stackoverflow.com/questions/20690965/how-do-i-get-values-for-all-months-in-t-sql) — Tab Alleman, Apr 22 '16 at 15:16

Alexey · Answer 1 · 2016-04-22T15:44:30.960

I'm not understand your goal fully. But you can try to use next approach

declare @start_date datetime = (select min(DateHeureMesure) from measure)
declare @due_date datetime = (select max(DateHeureMesure) from measure)

declare @months as table (
    the_year int
,   the_month int
)

declare @the_date datetime = @start_date

while @the_date<=@due_date
begin
    insert into @months values(datepart(yy, @the_date), datepart(mm, @the_date))
    set @the_date = dateadd(mm,1,@the_date)
end


select 
    m.*
,   data.grouping_field1
,   data.grouping_field2
,   data.agg_value1
,   data.agg_value2
from @months m
left join (
    select
        datepart(yy, m.DateHeureMesure) the_year
    ,   datepart(mm, m.DateHeureMesure) the_month
    ,   m.grouping_field1
    ,   m.grouping_field2
    ...
    ,   sum(m.agregated_field1) agg_value1
    ,   count(m.agregated_field2) agg_value2
    ...
    from measure m
    group by datepart(yy, m.DateHeureMesure)
    ,        datepart(mm, m.DateHeureMesure)
    ,        m.grouping_field1
    ,        m.grouping_field2
) data on data.the_year = m.the_year
    and   data.the_month = m.the_month
order by m.the_year, m.the_month

for better performance table @months can be physical table with indexes. But then not forget to support automatic expanding of it.
you can create generator function on server for @month table using "create function returns table" syntax. and then you'll be able to write single-statement SQL like (function name fn_GenMonths):

  select 
      m.*
  ,   data.grouping_field1
  ,   data.grouping_field2
  ,   data.agg_value1
  ,   data.agg_value2
  from fn_GenMonths(@start_date,@due_date) m
  left join (
        select
            datepart(yy, m.DateHeureMesure) the_year
        ,   datepart(mm, m.DateHeureMesure) the_month
        ,   m.grouping_field1
        ,   m.grouping_field2
        ...
        ,   sum(m.agregated_field1) agg_value1
        ,   count(m.agregated_field2) agg_value2
        ...
        from measure m
        group by datepart(yy, m.DateHeureMesure)
        ,        datepart(mm, m.DateHeureMesure)
        ,        m.grouping_field1
        ,        m.grouping_field2
  ) data on   data.the_year = m.the_year
        and   data.the_month = m.the_month
  order by m.the_year, m.the_month

Why so complicated? The @month table variable doesn't need the year, it only needs months, just 1-12. You can generate that much easier. — HoneyBadger, Apr 23 '16 at 15:46

score 0 · Answer 2 · edited May 15 '16 at 20:58

"It's works" but very slowly : left join on temp table with year and months :

select the_year, the_month--,NbDatesAvecDonnees,NbDatesTheoriques,NbDatesValides, DispoBrute, DispoValide,dispoBruteCorrigee,dispoValideCorrigee    
from @months
left join 
(
    select Annee, Mois,
    min(datedebut) as dtDebutPeriode, 
    SUM(nbDates) as NbDatesAvecDonnees,
    sum(NbDatesTheoriques) as NbDatesTheoriques,sum(NbDatesValides) as NbDatesValides,
    round(cast(SUM(nbDates) * 100 as float) / sum(NbDatesTheoriques),2) as DispoBrute,
    round(cast(SUM(NbDatesValides) * 100 as float) / sum(NbDatesTheoriques),2) as DispoValide,
    case when sum(nbDatesMinMax) != sum(NbDatesTheoriques) then round(cast(SUM(nbDates) * 100 as float) / sum(nbDatesMinMax),2) else round(cast(SUM(nbDates) * 100 as float) / sum(NbDatesTheoriques),2) end as dispoBruteCorrigee,
    case when sum(nbDatesMinMax) != sum(NbDatesTheoriques) then round(cast(SUM(NbDatesValides) * 100 as float) / sum(nbDatesMinMax),2) else round(cast(SUM(NbDatesValides) * 100 as float) / sum(NbDatesTheoriques),2) end as dispoValideCorrigee
    ,case when sum(nbDatesMinMax) != sum(NbDatesTheoriques) then 1 else 0 end as erreur
  from ( 
    select 
        dateMin, dateMax, (DATEDIFF(minute, dateMin, dateMax) + 10)/10 as nbDatesMinMax, 
        dateDebut, dateadd(second, -1 ,DATEADD(month, 1, datedebut)) as datefin, annee, Mois, 
        (DATEDIFF(minute, datedebut, dateadd(second, -1 ,DATEADD(month, 1, datedebut))) +10 )/10 as NbDatesTheoriques  , nbDates, NbDatesValides  
        from (
                select 
                    min(dateheuremesure) as dateMin, 
                    max(dateheuremesure) as dateMax,
                    DATEADD(day,-(datepart(day,(DATEADD(hour,-(datepart(hour,dateadd(minute,-(datepart(minute, min(dateHeureMesure))), min(dateheuremesure)))), dateadd(minute,-(datepart(minute, min(dateHeureMesure))), min(dateheuremesure)))))) + 1,
                    DATEADD(hour,-(datepart(hour,dateadd(minute,-(datepart(minute, min(dateHeureMesure))), min(dateheuremesure)))), dateadd(minute,-(datepart(minute, min(dateHeureMesure))), min(dateheuremesure))))
                    as dateDebut,
                    DATEPART(year, DateHeureMesure) as annee, 
                    DATEPART(month, dateheuremesure) as mois, TypeMesure, HauteurMesure, 
                    count(dateheuremesure) as nbDates,
                    SUM(CASE WHEN ValideeMesure = 2 THEN 1 ELSE 0 END) AS NbDatesValides
                from mesure                                 
                where IdCampagneMesure = @IdCampagne and DateHeureMesure between @start_date and @due_date              
                group by DATEPART(YEAR, DateHeureMesure), DATEPART(month, dateheuremesure), TypeMesure , HauteurMesure              
            ) r group by annee, dateMin,dateMax,dateDebut,mois,nbDates,NbDatesValides) r2 group by annee,mois
            )                               
    data on data.annee = the_year and data.mois = the_month

SQL Query Month / Year

2 Answers2