January 13, 2014 at 2:16 pm
Hello,
I have a query that aggregates a value based on a date range and excludes business hours. I need to INCLUDE Saturday and Sunday based on the date range. I am stuck and I think I am at that time of day where your brain decides to quit. Time for some coffee 🙂
Here is what I have so far and as always, thank you all very much for your help!
declare @StartDate datetime, @EndDate datetime
set @StartDate = '6/1/2013'
set @EndDate = '6/30/2013'
Select sum(abncalls)
from TABLE
where date between @StartDate) and (@EndDate)
and starttime not in (800,830,900,930,1000,1030,1100,1130,1200,1230,1300,1330,1400,1430,1500,1530,1600,1630)
January 13, 2014 at 2:27 pm
You've been a member of this forum long enough to know that you need to provide DDL, sample data and desired results in a consumable format in order to get a working answer.
Please follow the link in my signature if you need a reminder of how to do this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 13, 2014 at 2:29 pm
Hi Phil,
Nice to see you again 🙂
I just need to know what the syntax is to include weekends based on a simple date range. Not sure why you would need to see data for that. Please correct me if I am wrong. I read your post and I agree with you 100%, but this case seems straight forward enough.
Thanks
(Please pardon my typos today)
January 13, 2014 at 2:38 pm
Your code doesn't seem to exclude weekends, it only excludes hours. Since hours can happen in any given day, you're excluding those hours on weekends as well.
January 13, 2014 at 2:42 pm
Phil Parkin (1/13/2014)
You've been a member of this forum long enough to know that you need to provide DDL, sample data and desired results in a consumable format in order to get a working answer.Please follow the link in my signature if you need a reminder of how to do this.
For English installations...
OR DATENAME(dw,Date) IN ('Saturday','Sunday')
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2014 at 2:45 pm
I'm thinking that you might want to include "business hours" on weekends. The easiest way to do it would be using DATEPART. However, you might encounter with the problem of holidays and there's where a calendar table will help you.
You would end up with something like this depending on your calendar table.
Select sum(abncalls)
from TABLE t
JOIN CalendarTable c ON t.date = c.date
where date between @StartDate) and (@EndDate)
and (starttime not in (800,830,900,930,1000,1030,1100,1130,1200,1230,1300,1330,1400,1430,1500,1530,1600,1630)
or is_workday = 0)
January 13, 2014 at 10:43 pm
DaveDB (1/13/2014)
Hi Phil,Nice to see you again 🙂
I just need to know what the syntax is to include weekends based on a simple date range. Not sure why you would need to see data for that. Please correct me if I am wrong. I read your post and I agree with you 100%, but this case seems straight forward enough.
Thanks
(Please pardon my typos today)
Perhaps the reason for my request for additional material is becoming clearer now.
The fact that accomplished professionals, who would usually be able to answer this question while simultaneously juggling on a unicycle, are having to guess your requirements is proof enough.
(I am not one of those people, by the way :hehe:)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 14, 2014 at 7:21 am
Simple enough Phil.... And I didn't need a unicycle 🙂
Please refrain from patronizing people. Not everyone is an expert and we are all trying to learn at our own pace. I find your comment very insulting. It's very disturbing when the first thing you see in the morning is some egotistical guru throwing insults around because he has nothing better to do with his time. This forum was designed to help people out, not point out shortcomings.
BTW: Here is the answer. As basic as the question may have been. You see... This is called "learning".
DATEPART(dw,Datestamp) IN (1,7)))
January 14, 2014 at 8:21 am
DaveDB (1/14/2014)
Simple enough Phil.... And I didn't need a unicycle 🙂Please refrain from patronizing people. Not everyone is an expert and we are all trying to learn at our own pace. I find your comment very insulting. It's very disturbing when the first thing you see in the morning is some egotistical guru throwing insults around because he has nothing better to do with his time. This forum was designed to help people out, not point out shortcomings.
BTW: Here is the answer. As basic as the question may have been. You see... This is called "learning".
DATEPART(dw,Datestamp) IN (1,7)))
Don't feel insulted, it's only a SQL Server forum. Not only that, but I reread my post and there was no insult.
But there was a message:
Had you provided executable code when you asked the question, along with actual and expected outcomes, it would have been answered sooner and with less fuss.
I was hoping that you would take that on board for any future posts.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 14, 2014 at 8:41 am
I can't find the insult on Phil's comments. He's just asking you to provide better information when you post questions, it has nothing to do with your knowledge.
Jeff and I made some guesses that might have helped you (or not). However, I return to one question I had. Would you need to include holidays (Christmas, New Year, Independence Day, etc) as well?
January 15, 2014 at 8:05 am
DaveDB (1/14/2014)
DATEPART(dw,Datestamp) IN (1,7)))
Just one thing to watch out for DaveDB, the code above relies on your instance having Sunday as the first day of the week. That might not be the case on every instance/database, particularly if the default language for the isntance is not us_english, so you might want to explicitly set it as such by running:
SET DATEFIRST 7
You can check what the first day of the week is set to by running:
SELECT @@DATEFIRST
Regards
Lempster
January 24, 2014 at 9:07 am
This comment is very insulting.
"Perhaps the reason for my request for additional material is becoming clearer now.
The fact that accomplished professionals, who would usually be able to answer this question while simultaneously juggling on a unicycle, are having to guess your requirements is proof enough."
All I asked was.... "How can you filter Saturday and Sunday out of a query". You do not need a result set for this particular question and if you do.... well.... then it's on you. I am not looking for trouble, but in all honesty, you seem more interested in playing gatekeeper to SSC than actually answering questions. Did you really not understand my question? MSDN Forums didn't seem to have an issue.
* shrug
January 24, 2014 at 9:30 am
DaveDB (1/24/2014)
This comment is very insulting."Perhaps the reason for my request for additional material is becoming clearer now.
The fact that accomplished professionals, who would usually be able to answer this question while simultaneously juggling on a unicycle, are having to guess your requirements is proof enough."
All I asked was.... "How can you filter Saturday and Sunday out of a query". You do not need a result set for this particular question and if you do.... well.... then it's on you. I am not looking for trouble, but in all honesty, you seem more interested in playing gatekeeper to SSC than actually answering questions. Did you really not understand my question? MSDN Forums didn't seem to have an issue.
* shrug
Nonsense. Here's a gift for you to show that neither Phil nor anyone else who has responded on this thread means any ill will:
CREATE FUNCTION [dbo].[IF_Calendar]
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max
iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)
-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName]= DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
WHERE b.FirstWeekDay = @FirstWeekDay
)
GO
My son juggles whilst riding his unicycle. He earns cash from it, entertaining folks outside nightclubs. Nice little earner for a mech eng student.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 24, 2014 at 10:44 am
DaveDB (1/24/2014)
This comment is very insulting."Perhaps the reason for my request for additional material is becoming clearer now.
The fact that accomplished professionals, who would usually be able to answer this question while simultaneously juggling on a unicycle, are having to guess your requirements is proof enough."
All I asked was.... "How can you filter Saturday and Sunday out of a query". You do not need a result set for this particular question and if you do.... well.... then it's on you. I am not looking for trouble, but in all honesty, you seem more interested in playing gatekeeper to SSC than actually answering questions. Did you really not understand my question? MSDN Forums didn't seem to have an issue.
* shrug
I just can't find an insult here. You obviously can. None was meant, I assure you.
Here is my quote, reworded: had you provided a detailed and specific question, it would have been answered by skilled, knowledgeable and experienced professionals with no fuss and no need for supplementary questions and guesswork on their part.
That's it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply