SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Incluse Saturday and Sunday in calculation


Incluse Saturday and Sunday in calculation

Author
Message
DaveDB
DaveDB
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 215
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 Smile

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)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18428 Visits: 20425
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
DaveDB
DaveDB
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 215
Hi Phil,

Nice to see you again Smile


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)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16494 Visits: 19086
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16494 Visits: 19086
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)




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18428 Visits: 20425
DaveDB (1/13/2014)
Hi Phil,

Nice to see you again Smile


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 :heheSmile


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
DaveDB
DaveDB
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 215
Simple enough Phil.... And I didn't need a unicycle Smile

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)))
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18428 Visits: 20425
DaveDB (1/14/2014)
Simple enough Phil.... And I didn't need a unicycle Smile

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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16494 Visits: 19086
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?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search