SQLTeam.com | Weblogs | Forums

Addin Max(count) by month to my query


#1

My query is pulling count by week and I want to add another column that shows both the value at the `1st of the month' (on day 1) and the Max value in each month. Since there will be ~ 4 weeks, these values would show up 4 times but that's what I need. Query as follows:

SET DATEFIRST 1
select

DATEADD(WEEK, DATEDIFF(WEEK, 0, doa), 0) 'WeekOf',

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
department, left(department, 3), ''), 'NewHire_', ''), 'Account Services','ACCT Serv'), 'CC Services','CC Serv'),'Credit Cards','CC Serv'),'CL Services', 'Consumer Loans'),
'CC/CL Applications', 'Consumer Loans'), 'Fraud', 'CCO Fraud'), 'Mortgage Services', 'MTG Apps'),
'Mortgage Applications', 'MTG Apps') 'Department',

case when Office like '%Headquarters%' then 'HQ'
when Office like '%Heritage Oaks%' then 'HOCC'
when Office like '%Pensacola Pace%' then 'PPB'
when Office like '%Town/Country%' then 'PPB'
when Office like '%Winchester%' then 'WCC'
else Office
end 'Location',

'On Board' 'Type',

count(distinct empid) 'WeekCount',

from wfm_db..dimemployeehist

where doa in (select distinct datevalue+1 'Month' from ccodw..dimdate where datevalue between '20160101' and getdate())
and department not like 'BR_%'
and department not like 'CCO_Division'
and department not like '%Executive%'
and department not like '%_Training'
and department not like '%_Quality%'
and department not like '%_Correspondence'
and department not like 'Analyst'
and department not like '%Member Relations%'
and office is not null
and office <> 'Teleworking'

group by

DATEADD(WEEK, DATEDIFF(WEEK, 0, doa), 0),

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
department, left(department, 3), ''), 'NewHire_', ''), 'Account Services','ACCT Serv'), 'CC Services','CC Serv'),'Credit Cards','CC Serv'),'CL Services', 'Consumer Loans'),
'CC/CL Applications', 'Consumer Loans'), 'Fraud', 'CCO Fraud'), 'Mortgage Services', 'MTG Apps'),
'Mortgage Applications', 'MTG Apps'),
case when Office like '%Headquarters%' then 'HQ'
when Office like '%Heritage Oaks%' then 'HOCC'
when Office like '%Pensacola Pace%' then 'PPB'
when Office like '%Town/Country%' then 'PPB'
when Office like '%Winchester%' then 'WCC'
else Office
end