CODEWITHSUNDEEP
sqlsql-serverdatecase
Simon Thiesen
Simon Thiesen

Reputation: 1

SQL - Calculate 4 businessdays back in time

I have a question regarding time and time calculations.

I have created a DimDate table with the following dimensions:

Date       Day DaySuffix    Weekday WeekDayName IsWeekend   IsHoliday
2000-01-01  1    st            7    Saturday       1           1

I'm looking for a solution which tells SQL to include persons created more than 4 Businessdays ago (Excluding weekends and holidays) since creation. But i'm in doubt how to incorporate the DimDate into the equation below:

I have only figured out how to go back 4 days, but not excluding either weekends and holidays.

,case   
   when Person = 1 and CreationDate < DATEADD(day, -4, GETDATE())
     then 1
     else 0 
end as 'Missing'

Upvotes: 0

Views: 162

Answers (1)

Ross Bush
Ross Bush

Reputation: 15185

If I am reading your question correctly then you should be able to filter the data set based on valid days and give each record in the filtered set a ranking by date descending. This yields the number of days back, not including weekdays or holidays.

DECLARE @T TABLE(Date DATETIME, Weekday INT, IsWeekend BIT, IsHoliday BIT)

INSERT @T VALUES
('01/01/2000',7,1,1),
('12/31/1999',6,0,0),
('12/30/1999',5,0,0),
('12/29/1999',4,0,0),
('12/28/1999',3,0,0),
('12/27/1999',2,0,0),
('12/26/1999',1,1,0),
('12/25/1999',7,1,1),
('12/24/1999',6,0,0)

DECLARE @ReportDate DATETIME = '01/04/2000'
DECLARE @DaysBack INT = 4


SELECT
    *
FROM
(
    SELECT
        ValidDaysBack=ROW_NUMBER() OVER(ORDER BY Date DESC),* 
    FROM 
        @T
    WHERE
        (IsWeekend = 0 AND IsHoliday = 0) AND (Date <= @ReportDate)
)AS Data
WHERE
    ValidDaysBack >= @DaysBack

Upvotes: 1

Related Questions