asked
How to Calculate TOTAL business days between two days (SQL Server) (Particular case)
I'm trying to get how many days took some employees to complete their assignment. I'm doing this calculation based on Closing dates(ClosingDate) and Approve dates(ApprovalDate) and I have a particular date of the company, result of the weekdays (monday to friday), specific holidays of the company and standard holidays of USA (BSDAYS). Every time I try to add (BSDAYS) to the query its give me an error of grouping but I can't group by (BSDAYS) because the query will return a mess with a billions of records or it says Operand type clash: date is incompatible with int. This is the code I'm using for now which is working ok, excluding the holidays and weekends but not for specific holidays of the company: [DATES(FILE)][1] [BUSINESSDAYS(FILE)][2] SELECT ROW_NUMBER() OVER (ORDER BY Date_ID asc) BusinessDaysID, DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - ((DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - (6-DATEPART(DW,B.ClosingDate)))/7)*2 AS TOTALBUSINESSDAY FROM DIM_DATE B CROSS JOIN COMPANY_CALENDAR c GROUP BY Date_Key,B.ClosingDate,B.ApprovalDate [1]:
https://drive.google.com/file/d/1-4xXLjPfOYUty5AHsBoVIgYrZ4Bt9CwK/view?usp=sharing [2]:
https://drive.google.com/file/d/1KWuzymFgcSyraA_Myst7Rah1lfc3AEOl/view?usp=sharing
If BSDays are of type Date, then it should not be a problem. If they are stored as integer then you must convert it to date and then do the calculation. Moreover, your calculation for weekends is error prone, so I have used more accurate [logic][1] in the code (Thanks to Mr. Jeff Moden). declare @COMPANY_CALENDAR table ( BSDAYS date ) insert @COMPANY_CALENDAR select '20180115' union all select '20180219' declare @DIM_DATE table ( ClosingDate date, ApprovalDate date ) insert @DIM_DATE select '20180101', '20180301' union all select '20180109', '20180321' union all select '20180305', '20180316' union all select '20180309', '20180321' select B.ClosingDate,B.ApprovalDate, DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - ((DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - (6-DATEPART(DW,B.ClosingDate)))/7)*2 AS TOTALBUSINESSDAY, DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - (((DATEDIFF(DAY,B.ClosingDate,B.ApprovalDate) - (6-DATEPART(DW,B.ClosingDate)))/7)*2 ) - (select COUNT(*) from @COMPANY_CALENDAR where BSDAYS between B.ClosingDate and B.ApprovalDate) TBDaysExcludingBSDays, (DATEDIFF(dd,ClosingDate,ApprovalDate)+1) --Subtact 2 days for each full weekend -(DATEDIFF(wk,ClosingDate,ApprovalDate)*2) --If StartDate is a Sunday, Subtract 1 -(CASE WHEN DATENAME(dw,ClosingDate) = 'Sunday' THEN 1 ELSE 0 END) --If EndDate is a Saturday, Subtract 1 -(CASE WHEN DATENAME(dw,ApprovalDate) = 'Saturday' THEN 1 ELSE 0 END) - (select COUNT(*) from @COMPANY_CALENDAR where BSDAYS between B.ClosingDate and B.ApprovalDate) TBDaysExcludingBSDaysRecalculated FROM @DIM_DATE B GROUP BY B.ClosingDate, B.ApprovalDate **EDIT:** The DATE_KEY seems to be the primary key? If yes, then there is no need for grouping. declare @COMPANY_CALENDAR table ( BSDAYS date ) insert @COMPANY_CALENDAR select '1/4/2018' union all select '1/5/2018' union all select '1/19/2018' union all select '1/22/2018' union all select '1/23/2018' union all select '1/24/2018' union all select '1/25/2018' union all select '1/26/2018' union all select '1/29/2018' union all select '1/30/2018' declare @DIM_DATE table ( DATE_KEY INT, ClosingDate date, ApprovalDate date ) insert @DIM_DATE select 36937, '20180125', '20180119' union all select 37067, '20180105', '20180104' ; WITH Dates As ( select DATE_KEY, ClosingDate, ApprovalDate FROM @DIM_DATE B ) select b.*, TBDays * CASE WHEN B.ClosingDate > B.ApprovalDate THEN -1 ELSE 1 END TBDays from Dates b CROSS APPLY (select COUNT(*) TBDays from @COMPANY_CALENDAR where BSDAYS >= CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ApprovalDate ELSE B.ClosingDate END and BSDAYS < CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ClosingDate ELSE B.ApprovalDate END )r [1]:
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
@JCACERES If you would like the value of the column named TBDays to be NULL when it evaluates to zero then you can use nullif, i.e. change the line reading TBDays * CASE WHEN B.ClosingDate > B.ApprovalDate THEN -1 ELSE 1 END to read nullif(TBDays, 0) * CASE WHEN B.ClosingDate > B.ApprovalDate THEN -1 ELSE 1 END This way, if TBDays which came out of the cross apply is evaluated to 0 then applying nullif will cause it to be NULL and NULL multiplied by anything is still NULL. This is the reason why @Usman Butt used cross apply (no need to use outer apply): because the expression in the cross apply will always return a non-null value no matter what (count of NULL is zero, not null). For example: declare @x int = null; select count(@x) CountOfX; The result of it is this: CountOfX ----------- 0
I have edited my answer. See if it helps. Sorry but I am not able to go through all the data in the files, a bit busy at the moment. For better help in future, please post the DDL of the tables, some dummy data and the required output :)
18 People are following this question.
