Calculating Last 5 Business Days and Next 5Days

Question

Post reply

Calculating Last 5 Business Days and Next 5Days

koti.raavi

SSCrazy

Points: 2756
More actions
December 16, 2014 at 8:15 am

#150258

Hi All, Can you any help me out calculation of last 5 business days and next 5 business days. thanks
Example: Today Date is 16th Dec (Last 5 business days 10th,11th,12th,15th,16th dec)
(next 5 busines days 17th,18th,19th,22st,22nd)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You should use a calendar table for this type of thing. http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

Hi Sean, Thanks for your reply...Is there any way we can do same using Sql Query without using calender tables?..Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

koti.raavi (12/16/2014)
Hi Sean, Thanks for your reply...Is there any way we can do same using Sql Query without using calender tables?..Thanks

Sure but it is much more difficult and less flexible. Consider the "Last 5 Business Days" when holidays are involved. This starts to make the sql incredibly complicated and unflexible. Using a calendar table this becomes painless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

koti.raavi SSCrazy Points: 2756 More actions · Answer 4

Okay..thanks..! as of know i don't want to include holidays just last 5 business days and next 5 business days is enough..

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

koti.raavi (12/16/2014)
Okay..thanks..! as of know i don't want to include holidays just last 5 business days and next 5 business days is enough..

What defines business days? Monday - Friday? There are no exceptions to that rule ever?

I would still use a calendar table for this as it provides the flexibility even if you don't use it currently.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jshuter SSC Enthusiast Points: 170 More actions · Answer 6

Hi Rookie

If you want to take the direct approach, you'll need to loop through with DATEADD( ) function

then go forward with D, 1,

and backwards with D, -1,

and test that DATEPART() are not SAT or SUN

and you'll also use the GETDATE() function for todays date

Cheers

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

jshuter (12/16/2014)
Hi Rookie
If you want to take the direct approach, you'll need to loop through with DATEADD( ) function
then go forward with D, 1,
and backwards with D, -1,
and test that DATEPART() are not SAT or SUN
and you'll also use the GETDATE() function for todays date
Cheers

Better than looping would be to use a tally table. You could use -7 and +7 and exclude those where the day is saturday or sunday.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

shoestringdba SSCertifiable Points: 6226 More actions · Answer 8

I second Sean's answer. Calendar tables will save you time and effort in the long run.

For example, we have two different fiscal years (one calendar year based the other Federal Government fiscal year based), monthly "billing periods" which operate on "4-5-4" quarters and the 5 isn't always in the same place from quarter to quarter and a different holiday schedule than our parent company.

Any of which is subject to change whenever someone thinks about dropping a hat in the northern hemisphere (and sometimes southern). Trying to use various date/time functions to achieve all that just ain't gonna happen.

I've found once you've come up with some SQL wizardry for things like "last 5 business days", etc., someone will ask, "hey, could you do something similar except this time on a monthly, quarterly or yearly basis?"

At moments like those, you will thank yourself for having a calendar table of some sort around.

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Here is one way you could do this without using a loop. This uses the concepts of a tally table which you can read more about here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

WITH

E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

E2(N) AS (SELECT 1 FROM E1 a union all select 1 from E1 b), --10E+2 or 100 rows

cteTally(N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2

)

, MyDates as

(

select *--, 8 - N as N6

, dateadd(day, 8 - N, getdate()) as NewDate

from cteTally

where N <= 15

)

select NewDate

from myDates

where datepart(weekday, NewDate) in (2, 3, 4, 5, 6)

order by N desc

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jshuter SSC Enthusiast Points: 170 More actions · Answer 10

I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂

I'd still do something like the following

declare @i int = -7

declare @date date

set @date = GETDATE()

while @i < 7 begin

if datepart(weekday, dateadd(D, @i, @Date) ) in (2,3,4,5,6) begin

print dateadd(D, @i, @Date)

end

set @i+=1

end

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

jshuter (12/17/2014)
I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂
I'd still do something like the following
declare @i int = -7
declare @date date
set @date = GETDATE()
while @i < 7 begin
if datepart(weekday, dateadd(D, @i, @Date) ) in (2,3,4,5,6) begin
print dateadd(D, @i, @Date)
end
set @i+=1
end

Why???

I have to say that taking a set based solution and turning it into a loop is completely backwards. This is taking a fast approach and making it slower. While the performance difference on such a small set of data is negligible (if even measurable) using a loop is dangerous inmho. The reason I say that is because somebody else may come along and copy the code for use on a huge dataset. Now all of a sudden your looping code becomes a huge issue and it would have to be explained. Or...maybe this small dataset gets expanded to year and suddenly the performance is horrible.

You need to change your mindset to think about sets instead of rows.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

jshuter (12/17/2014)
I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂

I know thousands of people that agree with you... they've all posted requests for help with performance on this very site. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 13

jshuter (12/17/2014)
I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂

Um, yeah. It won't kill you to use a butter knife as a screwdriver either, but it's simply not the right tool for the job.

Don Simpson

I'm not sure about Heisenberg.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

jshuter (12/17/2014)
I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂
I'd still do something like the following
declare @i int = -7
declare @date date
set @date = GETDATE()
while @i < 7 begin
if datepart(weekday, dateadd(D, @i, @Date) ) in (2,3,4,5,6) begin
print dateadd(D, @i, @Date)
end
set @i+=1
end

It does appear that the looping syntax is easier on the eyes and easier to maintain. But the tally table syntax, aside from being a significantly more efficient process, is really not that complicated. Give yourself the opportunity to get familiar with it and it wont appear so daunting

----------------------------------------------------