question

JCACERES avatar image
JCACERES 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
sqltsqlsqlserverdatediff
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
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/
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@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
2 Likes 2 ·
Usman Butt avatar image Usman Butt commented ·
Can you please post the data for those specific cases?
1 Like 1 ·
Usman Butt avatar image Usman Butt commented ·
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 :)
1 Like 1 ·
JCACERES avatar image JCACERES commented ·
Thank you!!!!!!
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Hi, the code is working really good but I'm still seen differences for some case, for example I have result like -6 instead of -4...
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Yes give me a moment.
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
I just added the files which I'm using as a tables to take the business days, just to let you know the weekends and holidays from business days file are already removed so as you see those all the business days that I want to do the count.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Seems like my answer is totally different than what you require. If I understand correctly, COMPANY_CALENDAR is basically a Calendar table which has all the related years data already populated for each day? Can you please post the DDL of that table? So we can be sure of what it is.
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Sure, Yes COMPANY_CALENDAR Has already the final business days calculated. CREATE TABLE [dbo].[COMPANY_CALENDAR]( [BSDAYS] [date] NOT NULL, [ID] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [BSDAYS] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
If that is the case then it is pretty simple, you just need to count those business days which fall in Closing and Approval dates? Something like the following WITH Dates As ( select B.ClosingDate,B.ApprovalDate FROM DIM_DATE B GROUP BY B.ClosingDate, B.ApprovalDate ) select * from Dates b CROSS APPLY (select COUNT(*) TBDays from COMPANY_CALENDAR where BSDAYS between B.ClosingDate and B.ApprovalDate) BsDays
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Still not, for example in position 36937 of DATES(FILE) (DATE_KEY) should return -4 and is giving 0 and for 37067 should be -1 and is 0. I want to be able to count the days from COMPANY_CALENDAR between Closing and ApprovalDate groupy by DATE_KEY.
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Thank you so much, finally working with the Edited one !!!! Now I'm just trying to see how to avoid 0 when there are not values to compare. Should be NULL. Thanks a lot.
0 Likes 0 ·
JCACERES avatar image JCACERES commented ·
Thanks it really make sense to me.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.