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