Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Incluse Saturday and Sunday in calculation Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 13, 2014 2:16 PM
 SSC-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 datetimeset @StartDate = '6/1/2013'set @EndDate = '6/30/2013'Select sum(abncalls)from TABLEwhere 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:24 PM Points: 7,959, Visits: 19,062
Post #1530462
 Posted Monday, January 13, 2014 2:29 PM
 SSC-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
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 2:01 PM Points: 8,205, Visits: 17,750
 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
Post #1530471
 Posted Monday, January 13, 2014 2:42 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:17 AM Points: 42,051, Visits: 39,438
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1530472
 Posted Monday, January 13, 2014 2:45 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 2:01 PM Points: 8,205, Visits: 17,750
 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 tJOIN CalendarTable c ON t.date = c.datewhere 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
Post #1530475
 Posted Monday, January 13, 2014 10:43 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:24 PM Points: 7,959, Visits: 19,062
Post #1530538
 Posted Tuesday, January 14, 2014 7:21 AM
 SSC-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
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:24 PM Points: 7,959, Visits: 19,062