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:

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!

CodePudding user response：

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

CodePudding user response：

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!