Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Incluse Saturday and Sunday in calculation Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 2:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, 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 :)

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)
Post #1530458
Posted Monday, January 13, 2014 2:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530462
Posted Monday, January 13, 2014 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
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)
Post #1530466
Posted Monday, January 13, 2014 2:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530471
Posted Monday, January 13, 2014 2:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530472
Posted Monday, January 13, 2014 2:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530475
Posted Monday, January 13, 2014 10:43 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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 )



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530538
Posted Tuesday, January 14, 2014 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
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)))
Post #1530668
Posted Tuesday, January 14, 2014 8:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530711
Posted Tuesday, January 14, 2014 8:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530727
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse