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!

You say you have a calendar tabled but you don't know how to use it. Maybe one of your coworkers can explain how the table is supposed to be used? — Sean Lange, Aug 01 '22 at 14:30
Often there is some simple math you can apply to your calendar table to do just that. Posting DDL and some sample data for your calendar table would be a great help. — SMor, Aug 01 '22 at 14:33
Get the first day from your cal table that is older than 2 days, and is a workingday — GuidoG, Aug 01 '22 at 14:56
Just select the top 2 rows where "work day" is 1 and less than current date and then apply MIN (ROW_NUMBER is an alternative). Lots of tricks you can apply to a calendar table to implement logic like this which can be found with simple searching - especially if you have a frequent need for such. — SMor, Aug 01 '22 at 14:58

score 0 · Answer 1 · answered Aug 01 '22 at 15:01

You could use something like this:

SELECT  t.SalesDate,
        PreviousWorkingDay = d.CAL_DATE
FROM    mytable t
        CROSS APPLY
        (   SELECT  c.CAL_DATE
            FROM    D_Calendar AS c
            WHERE   c.CAL_DATE < t.SalesDate
            AND     c.DayIsWorkDay = 1
            ORDER BY c.CAL_DATE DESC OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY
        ) AS d;

It uses OFFSET 1 ROWS within the CROSS APPLY to get the penultimate working day

score 0 · Answer 2 · answered Aug 02 '22 at 07:15

This is how i implemented the idea from @SMor:

SELECT  

SalesAmount
,SalesDate

FROM mytable t

JOIN D_Calendar c ON t.Date = c.CAL_DATE

WHERE SalesDate <= (SELECT
    MIN(t1.CAL_DATE) as MinDate
  FROM
    (SELECT TOP 2
        [CAL_DATE]
    FROM [DWH_PROD].[cbi].[D_Calendar]
    WHERE CAL_DAYISWORKDAY = 1 AND CAL_DATE <  DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))
    ORDER BY CAL_DATE DESC
    ) t1)

Thank you for your ideas and recommendations!

score 0 · Answer 3 · answered Aug 03 '22 at 07:00

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

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

3 Answers3