Return Single Day of Week/Date for an annual query
Posted on 2013-01-03
I am creating a query to return the total of checks by week Saturday-Friday.
Checks may have been written on several dates during the week.
I only want to return the date for Thursday each week.
This did not work but should illustrate what I’m trying to do.
set DateFirst 5 -- Friday
datepart( ww, p.[CHECKDATE]) [Week]
, sum(p.[INSURANCE]) [Insurance]
, (select convert( varchar(10), p.[CHECKDATE], 1) where datepart( ww, p.[CHECKDATE]) = 4 ) [CheckDate]
from [PAYROLL] p
where p.[CHECKDATE] >= '01/01/2012'
and p.[CHECKDATE] < '01/01/2013'
and p.[ INSURANCE] <> 0.00
group by datepart( ww, p.[CHECKDATE]), p.[CHECKDATE]
set DateFirst 7 -- Always be sure to set it back