SQL Server select - complex case date statement-CodePudding

I need to write a select statement which says if the date range between dbo.activity.OpenDate and dbo.activity.CloseDate is less then 3 days Then 1 else 0. However I cannot include a non-working day. Therefore if it is a weekend or BH then it will change the calculation. All the non working days and format are in dbo.dates. Both tables are below.

dbo. dates

date	non-working day
01/01/2022	sat
12/05/2022	sun
08/09/2022	sun
03/04/2022	bank holiday

dbo. activity

open date	close date
12/03/2022	03/07/2022
30/05/2022	01/09/2022
01/09/2022	01/10/2022
02/04/2022	01/05/2022

expected output

A series of '1's or '0's if the criteria is met. The '1's and '0's can then be summed readily in the reporting medium...

CodePudding user response：

We could use a left anti-join approach here:

SELECT DISTINCT a.[open date], a.[close date],
       CASE WHEN d.date IS NULL THEN 0 ELSE 1 END AS flag
FROM activity a
LEFT JOIN dates d
    ON d.date BETWEEN a.[open date] AND a.[close date];

CodePudding user response：

select [open date], [close date],
   case when exists (select * from dates d
                     where d.[date] between a.[open date] and a.[close date])
        then 1 else 0 end as NonWorkingDayExists
from activity a;

EDIT: Rereading your revised question:

SELECT OpenDate, CloseDate, CASE WHEN COUNT([holiday].[date])<3 THEN 1 ELSE 0 END AS nwc
FROM activity a
     CROSS APPLY(SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate) 1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
                 FROM master.sys.all_columns t1
                      CROSS JOIN master.sys.all_columns t2) t(N)
     CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)) theDates(dt)
     OUTER APPLY(SELECT [date] FROM dbo.dates AS d WHERE d.date=theDates.dt) holiday([date])
GROUP BY  OpenDate, CloseDate;

However, your dates makes me think those sun, sat might mean weekends which then unnecessary store like that. You might instead get it checking date:

SELECT activityId, OpenDate, CloseDate, 
    CASE WHEN SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END) < 3 THEN 1 ELSE 0 END AS nonworking
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate) 1)
            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)
CROSS APPLY (SELECT (DATEPART(WEEKDAY, theDates.dt)  @@DATEFIRST)%7) weeks(wd)
OUTER APPLY (SELECT [date] FROM dbo.dates AS d WHERE d.date = theDates.dt) holiday([date])
GROUP BY activityId, OpenDate, CloseDate;

I added DBFiddle demo here.

EDIT: Explanation opn what we are doing:

SELECT OpenDate, CloseDate, CASE WHEN COUNT([holiday].[date])<3 THEN 1 ELSE 0 END AS nwc
FROM activity a
     CROSS APPLY(SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate) 1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
                 FROM master.sys.all_columns t1
                      CROSS JOIN master.sys.all_columns t2) t(N)
     CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)) theDates(dt)
     OUTER APPLY(SELECT [date] FROM dbo.dates AS d WHERE d.date=theDates.dt) holiday([date])
GROUP BY  OpenDate, CloseDate;

Let's dissect this to see how it is working:

SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate) 1) ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
                     FROM master.sys.all_columns t1
                          CROSS JOIN master.sys.all_columns t2) t(N)
         CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)

assuming dates difference in days OpenDate, CloseDate for a particular row is 9 we have (9 1) 10 days including OpenDate, this part of SQL would return a 'table' like:

N

1 2 3 4 5 6 7 8 9 10

CROSS APPLY allows us to do this per row, so for first row we have 1,2...114 days, second 95 and so on. You can see this executing that part only (in fiddle or your local SSMS):

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate) 1)
            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2) t(N)

Cross and Outer apply are easy and cheap ways running operations per row. Next cross apply build upon the previous and creates date of days column (theDates(dt)):

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate) 1)
            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)

and then next cross apply adds another column for weekday in a "SET DATE FIRST" neutral way (so 0 is always Saturday, 1 Sunday and so on).

Then comes an OUTER APPLY adding holidays to previous:

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate) 1)
            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)
CROSS APPLY (SELECT (DATEPART(WEEKDAY, theDates.dt)  @@DATEFIRST)%7) weeks(wd)
OUTER APPLY (SELECT [date] FROM dbo.dates AS d WHERE d.date = theDates.dt) holiday([date])

The difference between CROSS and OUTER APPLY is, CROSS APPLY is like inner join and needs a match, Outer apply is like Left join, doesn't need a match and column values are null when there is no match.

If you run it, you would see the 'table' we are working with. All there left to it is doing a simple aggregation doing group by. You have it in final state of the two SQLs.

In one of them we are only caring the dates which have a matching date in dates table, thus :

COUNT([holiday].[date])

is all we need is to check and put it in a case statement to be less than 3. This works, because rows having NULL for holiday.date would not be counted.

If what we needed was to check if the date is a saturday, sunday (0 and 1 weekday respectively) or the date is in dates table then we do:

SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END)

for counting matching rows and compare if it is less than 3 with a case statement.

I hope it is clear now.

This DBFiddle shows the steps. But be careful you would need to scroll to see it all.

CodePudding user response：

If I understand you correct, you want to assign a 1 if there are less than 3 business days (not just any calendar days) between open and close dates. Assuming I got that right, could you not subtract the count of non-working days that fall between open and close dates from the diff to make sure it accounts for non-working days?

select a.opendate, 
       a.closedate, 
       case when datediff(day, a.opendate, a.closedate)-count(b.nonworkingday) <3 then 1 else 0 end as flag
from activity a
left join dates b on b.date between a.opendate and a.closedate
group by a.opendate, a.closedate