Nested Query?

Transact-SQL
1

I have a table BusinessClosedDate with field startdate ( datetime)

What I am trying to do is get the next business day that the company is open
Ie . If tomorrow is a Sautrday or Sunday , date add Getdate() .. until the day is a weekday, then check the result against the startdate in BusinessClosedDate. if the dates match ...keep dateadd 1 day until the result is NOT a Saturday or Sunday and NOT in BusinessClosedDate

ie if today = 28/04/2017 and 01/05/2017 & 02/05/2017 are in BusinessClosedDate the result I'd be looking for would be 03/05/2017.

29/04/2017 = Saturday
30/04/2017 = Sunday
01/05/2017 In BusinessClosedDate
02/05/2017 in BusinessClosedDate

Nest day Business is open would be 03/05/2017

any help on getting this result would be very helpful

2

Something like this:

;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT ca1.next_business_day
FROM dbo.your_data_table ydt
CROSS APPLY (
SELECT TOP (1) DATEADD(DAY, t.number, GETDATE()) AS next_business_day
FROM cteTally100 t
WHERE DATEADD(DAY, 0, DATEADD(DAY, t.number, GETDATE())) % 7 < 5 AND
NOT EXISTS(SELECT 1 FROM dbo.BusinessClosedDate bcd WHERE bcd.startdate = DATEADD(DAY, t.number, GETDATE())
ORDER BY t.number
) AS ca1