SQLTeam.com | Weblogs | Forums

Get 2 business days

Hello,

I'm limited with access to SQL Server. I cannot create/execute functions/stored procedures/permanent tables. I'm working with temp tables only.

My query returns thousands of records and it is stored table name #Pop1. One of the column is a date column name EntryDate. I need to calculate 2 business days date (no holidays/weekends) from EntryDate for all records. I already created a temp table which list all days of year and I would be able to tell if a certain date is a business day or not. My query of getting 2 business days is below.

SELECT MAX(DataDate)
FROM (SELECT TOP(2)*
FROM ##tempCalendar
WHERE DataDate > '09-04-2020' AND IsBusinessDay = 1) a;

I'm struggling with how do I join the query from above to table #Pop1 that has all my population that I need, replacing the date '09-04-2020' from the query above to the column name EntryDate in table #Pop1?

Please help.

Thank you.

SELECT ...,
    (SELECT MAX(DataDate)
     FROM (SELECT TOP (2) C.DataDate 
           FROM ##tempCalendar C
           WHERE DataDate > P.date_column AND IsBusinessDay = 1
           ORDER BY C.DataDate
          ) AS derived1 
     ) AS finalColumnName
FROM #Pop1 P

Thank you so much ScottPletcher! It works! I can't tell you how appreciated I am! I've been struggling with this yesterday and it gives me such headaches!

Thank you for all your help! May you always be blessed with great happiness, prosperity and longevity!

Warm Regards,
Siumui

Great, glad it helped!

You rock! :slight_smile: