troubleshooting Question
Return Single Day of Week/Date for an annual query
asked on
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
select
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
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
select
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
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.