sql server - multiple counts with sql -


i have 2 tables, gages , schedule, , joined on company , serial number (gage_sn).

i attempting total count each month, next 3 months, , total 3 months cumulatively below:

type              month     next month     2 month     total ----              ----------     ----------     ------------------     ----- indicators            4              8                  2               14 calipers              0              16                 16              32 ... 

i have following total count of gages, type, due month:

 select g.gage_type, count(g.gage_type)    gages g         left outer join schedgi s on s.company = g.company , s.gage_sn = g.gage_sn   datepart(m, s.sched_due_date) = datepart(m, dateadd(m, +2, getdate()))     , datepart(yyyy, a.sched_due_date) = datepart(yyyy, dateadd(m, +2, getdate()))     , s.sched_type = 'calibration'     , g.company = 'abc compant'     , g.isactive = '1'     , g.event_status <> 'msi'     , g.event_status <> 'awrep'     , g.event_status <> 'lost'     , g.event_status <> 'oot'     , g.event_status <> 'cal.'     , g.event_status <> 'repair'     , g.event_status <> 'retool'     , g.event_status <> 'scrap'   group g.gage_type   order g.gage_type 

how modify sql desired result?

it's not particularly elegant, short of doing pivot, use case statements , sum.

since count() returns number of rows in group by, useful 1 grouping.

select    gages.gage_type,    sum(case      when (datepart(m, schedgi.sched_due_date) = datepart(m, getdate())          , ( datepart(yyyy, schedgi.sched_due_date) = datepart(yyyy, dateadd(m, +2, getdate()))) 1     else 0   end) 'thismonth',   sum(case      when (datepart(m, schedgi.sched_due_date) = datepart(m, dateadd(m, +1, getdate())))          , ( datepart(yyyy, schedgi.sched_due_date) = datepart(yyyy, dateadd(m, +2, getdate()))) 1     else 0   end) 'onemonths',   sum(case      when (datepart(m, schedgi.sched_due_date) = datepart(m, dateadd(m, +2, getdate())))          , ( datepart(yyyy, schedgi.sched_due_date) = datepart(yyyy, dateadd(m, +2, getdate()))) 1     else 0   end) 'twomonths'   --count(gages.gage_type)    gages gages  left outer join    schedgi schedgi  on     (schedgi.company = gages.company) , (schedgi.gage_sn = gages.gage_sn)    ( schedgi.sched_type = 'calibration' )   , ( gages.company = 'abc compant' )   , ( gages.isactive = '1' )   , ( gages.event_status <> 'msi')   , ( gages.event_status <> 'awrep' )   , ( gages.event_status <> 'lost' )   , ( gages.event_status <> 'oot' )   , ( gages.event_status <> 'cal.' )   , ( gages.event_status <> 'repair' )   , ( gages.event_status <> 'retool' )   , ( gages.event_status <> 'scrap' ) group     gages.gage_type order    gages.gage_type 

Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -