How to get Current 3 Business days data

Question

How to get Current 3 Business days data

fara

SSC Rookie

Points: 27
More actions
January 11, 2016 at 6:39 am

#314849

Hi Guys, I need your help, I'm trying to create a script that will get Current 3 Business days data, what's the best approach please?
Today is 11/01/2016- Monday
I need to get todays data(11/01/2016) and previous 2BD data, (07/01/2016 and 08/01/2016).
thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

fara (1/11/2016)
Hi Guys, I need your help, I'm trying to create a script that will get Current 3 Business days data, what's the best approach please?
Today is 11/01/2016- Monday
I need to get todays data(11/01/2016) and previous 2BD data, (07/01/2016 and 08/01/2016).
thanks.

Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TODAY DATE = GETDATE();

SELECT TOP (3)

DATEADD(DAY,X.N,@TODAY) AS BUZ_DAY

FROM (

SELECT 0 UNION ALL

SELECT -1 UNION ALL

SELECT -2 UNION ALL

SELECT -3 UNION ALL

SELECT -4

) AS X(N)

WHERE (DATEDIFF(DAY,0,DATEADD(DAY,X.N,@TODAY)) % 7) NOT IN (5,6)

ORDER BY DATEADD(DAY,X.N,@TODAY) DESC;

Results

BUZ_DAY

----------

2016-01-11

2016-01-08

2016-01-07

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

And here as an inline table-valued function

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

IF OBJECT_ID(N'dbo.ITVFN_GET_LAST_THREE_BUSINESS_DAYS') IS NOT NULL DROP FUNCTION dbo.ITVFN_GET_LAST_THREE_BUSINESS_DAYS;

GO

CREATE FUNCTION dbo.ITVFN_GET_LAST_THREE_BUSINESS_DAYS

(

@TODAY DATE

)

RETURNS TABLE

WITH SCHEMABINDING

AS

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

-- 2016-01-11 Eirikur Eiriksson

-- dbo.ITVFN_GET_LAST_THREE_BUSINESS_DAYS

-- Finding the last three days including the current day which are

-- neither Saturdays or Sundays. The logic is based on the fact that

-- Day 0, 1900-01-01 was a Monday which means that datediff from the

-- current day mod 7 will return 5 and 6 for Saturdays and Sundays

-- respectfully.

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

RETURN

SELECT TOP (3)

DATEADD(DAY,X.N,@TODAY) AS BUZ_DAY

FROM (

SELECT 0 UNION ALL

SELECT -1 UNION ALL

SELECT -2 UNION ALL

SELECT -3 UNION ALL

SELECT -4

) AS X(N)

WHERE (DATEDIFF(DAY,0,DATEADD(DAY,X.N,@TODAY)) % 7) NOT IN (5,6)

ORDER BY DATEADD(DAY,X.N,@TODAY) DESC;

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 3

Quick question... Do you need to exclude holidays as well?

fara SSC Rookie Points: 27 More actions · Answer 4

Exactly what I'm looking for!, thank you so much!

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

fara (1/12/2016)
Exactly what I'm looking for!, thank you so much!

You are very welcome.

😎

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 6

If "business day" = "weekday" then call it that in your code. "business day" implies you're accounting for things like holidays or other events proprietary to your business.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato