In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
- +1
9 Comments
Active 3 days ago
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:
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.
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
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.
Active 2 days ago
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.
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.
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
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.
Active 2 days ago
To circumvent the timestamp issue as mentioned by the expert above, I would typically do something like:
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.
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'
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.
Active today
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?
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?
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:)
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:)
Active 2 days ago
Ah, that makes more sense. Then you can generate your dates like this:
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.
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'
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.
No silver bullet, just good old fashioned code.
use myDataBase
go
/****** Object: StoredProcedure [dbo].[sp_pr_insurance_jou
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_pr_i
DROP PROCEDURE [dbo].[sp_pr_insurance_jou
go
/*
sp_pr_insurance_journal_am
11:22 PM 1/7/2013gr
*/
create proc [dbo].[sp_pr_insurance_jou
as
begin
declare @InsuranceWithHolding table
(
[Rec#] [int] IDENTITY (1, 1)
, [Journal Date] datetime
, [Insurance Total] decimal(10,2)
)
while ( @dStartDate <= @dEndDate) begin
if (datename(dw, @dStartDate ) = 'Thursday') begin
insert into @InsuranceWithHolding (
[Journal Date]
, [Insurance Total])
select
convert (varchar( 12), @dStartDate, 1) -- [Journal Date]
, sum([SECTION135CAFETERIAAM
from [PAYROLL]
where [CHECKDATE] >= @dStartDate - 6
and [CHECKDATE] <= @dStartDate
end
set @dStartDate = @dStartDate + 1
end
select
[Rec#]
, convert( varchar(12), [Journal Date], 1) [Journal Date]
, [Insurance Total]
from @InsuranceWithHolding
end
go
grant execute on [dbo].[sp_pr_insurance_jou
go
use myDataBase
go
/****** Object: StoredProcedure [dbo].[sp_pr_insurance_jou
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_pr_i
DROP PROCEDURE [dbo].[sp_pr_insurance_jou
go
/*
sp_pr_insurance_journal_am
11:22 PM 1/7/2013gr
*/
create proc [dbo].[sp_pr_insurance_jou
as
begin
declare @InsuranceWithHolding table
(
[Rec#] [int] IDENTITY (1, 1)
, [Journal Date] datetime
, [Insurance Total] decimal(10,2)
)
while ( @dStartDate <= @dEndDate) begin
if (datename(dw, @dStartDate ) = 'Thursday') begin
insert into @InsuranceWithHolding (
[Journal Date]
, [Insurance Total])
select
convert (varchar( 12), @dStartDate, 1) -- [Journal Date]
, sum([SECTION135CAFETERIAAM
from [PAYROLL]
where [CHECKDATE] >= @dStartDate - 6
and [CHECKDATE] <= @dStartDate
end
set @dStartDate = @dStartDate + 1
end
select
[Rec#]
, convert( varchar(12), [Journal Date], 1) [Journal Date]
, [Insurance Total]
from @InsuranceWithHolding
end
go
grant execute on [dbo].[sp_pr_insurance_jou
go
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_
@dStartDate
,@dEndDate
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_
@dStartDate
,@dEndDate
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 4 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.