We help IT Professionals succeed at work.

Return Single Day of Week/Date for an annual query

596 Views
Last Modified: 2013-01-13
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
Comment
Watch Question

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Based on this stackoverflow article:

http://stackoverflow.com/questions/3839923/get-date-of-all-saturdays-in-a-given-year-sql-server

The following query would return you all the Thursdays in 2012:

declare @d datetime
declare @d = '20120101'

select dateadd(dd,number,@d)
from master..spt_values
where type = 'P'
and year (dateadd(dd,number,@d) = year(@d)
and datepart(dw,dateadd(dd,number,@d)) = 5

Open in new window


So maybe you could have your base query for your checks and add a final part to it of something like

and CHECKDATE in (the select above)

You'd need to do a little date manipulation to truncate the dates to midnight of the dates returned only or convert to char with just the dates (which is horrible on indexing), but hopefully that gives you a good starting point.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Not sure you need to add in a dates table in this instance as I believe you just want the dates on Thursday within your data. If I am correct, try the following.

set DateFirst 5 -- Friday

select
    DATEPART(ww, p.[CHECKDATE]) [Week]
  , SUM(p.[INSURANCE]) [Insurance]
  , p.[CHECKDATE] [CheckDate]
from [PAYROLL] p
where p.[CHECKDATE] >= '01/01/2012'  
and p.[CHECKDATE] < '01/01/2013'
and p.[ INSURANCE] <> 0.00
and DATEPART(ww, p.[CHECKDATE]) = 4
group by p.[CHECKDATE]

set DateFirst 7 -- Always be sure to set it back

Open in new window


The performance will suffer because of the function on the p.[CHECKDATE] column, but the above should do what you desire. You could try with the table of dates LEFT OUTER JOIN [PAYROLL] ON p.[CHECKDATE] = [Thursday Date] to see if it performs better as it probably will even though you have to introduce more code.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
To circumvent the timestamp issue as mentioned by the expert above, I would typically do something like:

select dateadd(dd, number, '2012') as StartDT
     , dateadd(dd, number+1, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Thursday'

Open in new window


Then in the JOIN, I would use:
ON p.[CHECKDATE] >= d.StartDT and p.[CHECKDATE] < d.EndDT

You could then group on d.StartDT to ensure you got every record for that day regardless of timestamp.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
It looks to me like your weeks really start on Saturday not Friday, so I guess don't fully understand in which week to add checks for each day.

If a check is written on Friday, does it count in the NEXT Thu's total or the PREVIOUS Thu's?

For example, should a check written on Fri, Dec 21 be included in Dec 20's total or Dec 27's?

If a check is written on Saturday, does it count in the NEXT Thu's total or the PREVIOUS Thu's?

For example, should a check written on Sat, Dec 22 be included in Dec 20's total or Dec 27's?
Calvin DayEngineer, Programmer

Author

Commented:
Hi Scott,

Our payroll period ends / journals on Thursday at midnight.
These other suggestions would drop the insurance amounts on checks not printed on Thursday.
I’m looking to accumulate all checks insurance for the journal week, only returning the journal date.
Typically things like this I would create a temp table with the weeks then, accumulate the totals on a second pass.
Just looking for a silver bullet. Nice to hear from you, thanx:) Greg
PS this is Obama care making work for us, at least it pays well:)
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Ah, that makes more sense. Then you can generate your dates like this:

select dateadd(dd, number-7, '2012') as StartDT
     , dateadd(dd, number, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Friday'

Open in new window


This gives you the date for Friday at midnight as well as the previous week's Friday at midnight. You JOIN to your data as:
ON p.[CHECKDATE] >= d.StartDT and p.[CHECKDATE] < d.EndDT

This will give you all the insurance checks written after last Friday at midnight up to (but not including) this Friday at midnight.
Engineer, Programmer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Calvin DayEngineer, Programmer

Author

Commented:
DECLARE @RC int
DECLARE @dStartDate datetime
DECLARE @dEndDate datetime

-- TODO: Set parameter values here.
set @dStartDate = '01/01/2012'
set @dEndDate = '12/31/2012'

EXECUTE @RC = [myDataBase].[dbo].[sp_pr_insurance_journal_amounts]
   @dStartDate
  ,@dEndDate
Calvin DayEngineer, Programmer

Author

Commented:
It works

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.