Based on todays date, how to get the date of the penultimate working day?

Question

I try to figure out, how I can get the penultimate workingday from todays date.

In my query, I would like to add an where clause where a specific date is <= today´s date minus 2 working days.

Like:

SELECT  

    SalesAmount
   ,SalesDate
  
FROM mytable t

JOIN D_Calendar c ON t.Date = c.CAL_DATE

WHERE SalesDate <= GETDATE()- 2 workingdays

I have a calendar table with a column "isworkingDay" in my database and I think i have to use this but i don´t know how?!

Structure of this table is like:

CAL_DATE	DayIsWorkDay
2022-07-28	1
2022-07-29	1
2022-07-30	0
2022-07-31	0
2022-08-01	1

One example: Today is Monday, August 01, 2022. So based on today, I need to get Thursday, July 28 2022. My desired result in the where clause should get me something like this:

where SalesDate<= Getdate() minus 2 workingdays

Thanks for your ideas!

Answer 1

You can use a ROW_NUMBER() OVER(ORDER BY CAL_DATE desc) getting get the top 2 rows then take the row with number 2. Example:

-- setup
Declare @D_Calendar as Table (CAL_DATE date, DayIsWorkDay bit)

insert into @D_Calendar values('2022-07-27', 1)
insert into @D_Calendar values('2022-07-28', 1)
insert into @D_Calendar values('2022-07-29', 1)
insert into @D_Calendar values('2022-07-30', 0)
insert into @D_Calendar values('2022-07-31', 0)
insert into @D_Calendar values('2022-08-01', 1)

Declare @RefDate DateTime = '2022-08-01 10:00'
 
-- example query
Select CAL_DATE
From 
    (Select top 2 ROW_NUMBER() OVER(ORDER BY CAL_DATE desc) AS BusinessDaysBack, CAL_DATE
    from @D_Calendar 
    where DayIsWorkDay = 1
    and CAL_DATE < Cast(@RefDate as Date)) as Data
Where BusinessDaysBack = 2

From there you can plug that into your where clause to get :

SELECT  
    SalesAmount
   ,SalesDate
FROM mytable t
WHERE SalesDate <= (Select CAL_DATE
                    From (Select top 2 ROW_NUMBER() OVER(ORDER BY CAL_DATE desc) AS BusinessDaysBack, CAL_DATE
                        from D_Calendar 
                        where DayIsWorkDay = 1
                        and CAL_DATE < Cast(getdate() as Date)) as Data
                    Where BusinessDaysBack = 2)

Change the 2 to 3 to go three days back etc