# DateDiff Query in sql server

Need to write a query to check for business day difference and need a count.

Please see the attached file.  Based upon that I need to take all the accounts that fall in the date range. (I will pass the start date and end date as paramenters to the query). So lets say in our case date range is 8-1-2018 to 8-31-2018

So based upon our example in the file rows 1,4,5,6,7,8,9,10 will be selected

step 2) I need to check if each Date (Date1, Date2 and Date3) meet the Threshold in business days and based upon that we will have 6 different counts.

for row 1 :
account open date = 8-1-2018 and Date1 = 8-3-2018.   The difference of these two dates is 2 business days.  So it is less then or equal to Date1Threshold (2).  So we will have a count of

Date1Threshold = 1  and DoesNotMeetThresholdforDate1 = 0

account open date = 8-1-2018 and Date2 = 8-9-2018.   The difference of these two dates is 6 business days.  So it is less then or equal to Date2Threshold (10).  So we will have a count of

Date2Threshold = 1  and DoesNotMeetThresholdforDate2 = 0

account open date = 8-1-2018 and Date3 = 8-19-2018.   The difference of these two dates is 12 business days.  So it is less then or equal to Date3Threshold (20).  So we will have a count of

Date3Threshold = 1  and DoesNotMeetThresholdforDate3 = 0

for row 4 :
account open date = 8-15-2018 and Date1 = 8-16-2018.   The difference of these two dates is 1 business days.  So it is less then or equal to Date1Threshold (2).  So we will have a count of

Date1Threshold = 2 and DoesNotMeetThresholdforDate1 = 0

account open date = 8-15-2018 and Date2 = null .   Since Date2 is null we will have to take today's date(8-27-2018) into consideration and so the difference of these two dates is 8 business days.  So it is less then or equal to Date2Threshold (10).  So we will have a count of

Date2Threshold = 2  and DoesNotMeetThresholdforDate2 = 0

account open date = 8-15-2018 and Date3 = null.  Since Date3 is null we will have to take today's date(8-27-2018) into consideration and so the difference of these two dates is 8 business days.  So it is less then or equal to Date3Threshold (20).  So we will have a count of

Date3Threshold = 2  and DoesNotMeetThresholdforDate3 = 0

for row 5 :
account open date = 8-10-2018 and Date1 = 8-19-2018.   The difference of these two dates is 5 business days.  So it is greater then Date1Threshold (2).  So we will have a count of

Date1Threshold = 2  and DoesNotMeetThresholdforDate1 = 1

account open date = 8-10-2018 and Date2 = 8-28-2018.   The difference of these two dates is 11 business days.  So it is greater then  Date2Threshold (10).  So we will have a count of

Date2Threshold = 2  and DoesNotMeetThresholdforDate2 = 1

account open date = 8-10-2018 and Date3 = null.   Since Date3 is null we will have to take today's date(8-27-2018) into consideration and so the difference of these two dates is 10 business days.  So it is less then or equal to Date3Threshold (20).  So we will have a count of

Date3Threshold = 3  and DoesNotMeetThresholdforDate3 = 0

And thats how we continue calculation for each row.

at the end i should have a resultset of

count of Date1Threshold , count of DoesNotMeetThresholdforDate1, count of Date2Threshold , count of DoesNotMeetThresholdforDate2,count of Date3Threshold , count of DoesNotMeetThresholdforDate3
Book1.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQL Server Data DudeCommented:

Good luck.
Author Commented:
i just created temp table for holidays and comparing with that table.

Experts Exchange Solution brought to you by