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
PratikShah111Asked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without reading your question in detail (sorry), try reading the article SQL Server Calendar Table, downloading all of the code to your SSMS, modify to suit your personal definition of business days, and let us know if that works for you.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.