You are here:

MS SQL Server/Finding Business Days between dates in SQL Server 2008

Advertisement


Question
Results
Results  
Good Afternoon.... I am trying to find the eligible days worked  between two dates (hire date and period end date) for new hires for a bi-weekly payroll. I am using the following code
[(DATEDIFF(dd, eedatelasthire,@PPDtEnd) + 1)-(DATEDIFF(wk, eedatelasthire, @PPDtEnd) * 2)-(CASE WHEN DATEPART(dw,@PPDtBeg) = 1 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(dw, @PPDtEnd) = 7 THEN 1 ELSE 0 END) as DaysEligible]

and this does seem to work as long as the staff start within the first week. If they start during the second week, the results seem to be off by 1 day

Here is the script

Declare @PPDtBeg varchar(12),
  @PPDtEnd varchar(12)
     
Set @PPDtBeg = '2015-06-21'
Set    @PPDtEnd = '2015-07-04'

Select
ISNULL(EbClock,'') as Clock,
ISNULL(CONVERT (Varchar(12), eedatelasthire, 101), '') as [Recent Hire Date],  
ISNULL(CONVERT (Varchar(12), @PPDtBeg, 101), '') as PPDTBeg,
ISNULL(CONVERT (Varchar(12), @PPDtEnd, 101), '') as PPDTEnd,  
(DATEDIFF(dd, eedatelasthire,@PPDtEnd) + 1)-(DATEDIFF(wk, eedatelasthire, @PPDtEnd) * 2)-(CASE WHEN DATEPART(dw,@PPDtBeg) = 1 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(dw, @PPDtEnd) = 7 THEN 1 ELSE 0 END) as DaysEligible,  
(DATEDIFF(dd, @PPDtBeg,@PPDtEnd) + 1)-(DATEDIFF(wk, @PPDtBeg,@PPDtEnd) * 2)-(CASE WHEN DATEPART(dw, @PPDtBeg) = 1 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(dw, @PPDtEnd) = 7 THEN 1 ELSE 0 END) as PPDays,  
(ecomp.EmPerPay/((DATEDIFF(dd, @PPDtBeg,@PPDtEnd) + 1)-(DATEDIFF(wk,@PPDtBeg, @PPDtEnd) * 2)-(CASE WHEN DATEPART(dw, @PPDtBeg) = 1 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(dw, @PPDtEnd) = 7 THEN 1 ELSE 0 END))*((DATEDIFF(dd, eedatelasthire,@PPDtEnd) + 1)-(DATEDIFF(wk, eedatelasthire, @PPDtEnd) * 2)-(CASE WHEN DATEPART(dw, @PPDtBeg) = 1 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(dw, @PPDtEnd) = 7 THEN 1 ELSE 0 END))) as Prorate  


FROM ebase as ebase
INNER JOIN eemploy as eemploy ON eemploy.EeFlxIDEb = ebase.EbFlxID AND eemploy.EeDateEnd IS NULL
INNER JOIN ecomp as ecomp ON EmFlxIDEb = ebase.EbFlxID AND ecomp.EmFlxID =
(SELECT TOP 1 Em1.EmFlxID FROM ecomp as Em1 WHERE Em1.EmFlxIDEb = ebase.EbFlxID AND ecomp.EmKind = 'BASE' ORDER BY EmDateBeg Desc)

where eedatelasthire BETWEEN @PPDtBeg AND @PPDtEnd


any assistance would be greatly appreciated

Thank You for your assistance

Answer
I might be missing something, but I'm not seeing a problem (except when the last hire date is a Sunday, which probably shouldn't happen).

I'm assuming by "eligible days" you mean weekdays. I distilled the applicable code snippet into a SELECT using all @variables for ease of experimentation. Additionally, I found some code that calculates weekdays between 2 dates (NumberOfWeekdays in the code below). I ran it multiple times with varying dates and couldn't see a problem (outside of the aforementioned start-on-Sunday issue).  If you can reproduce the problem with some specific begin/end/hire date values, let me know and I'll take another look. (Note that the added code does not require the period begin date).

Here's the code I used:  


declare @ppdtend datetime
declare @ppdtbeg datetime


set @ppdtbeg = '7/1/2015'
set @ppdtend = '7/15/2015'

declare @eedatelasthire datetime
set @eedatelasthire = '7/1/2015'

select @eedatelasthire,
(DATEDIFF(dd, @eedatelasthire,@PPDtEnd) + 1)-(DATEDIFF(wk, @eedatelasthire, @PPDtEnd) * 2)-
  (CASE WHEN DATEPART(dw,@PPDtBeg) = 1 THEN 1 ELSE 0 END)-
  (CASE WHEN DATEPART(dw,@PPDtEnd) = 7 THEN 1 ELSE 0 END) as DaysEligible

  ,

DATEDIFF(DAY, @eedatelasthire, @PPDtEnd) + 1
         + (DATEDIFF(DAY, '1/1/1900', @eedatelasthire) + 1) / 7 * 2
         - (DATEDIFF(DAY, '1/1/1900', @PPDtEnd) + 1) / 7 * 2
         - CASE DATEDIFF(DAY, '1/1/1900', @eedatelasthire) % 7
         WHEN 6 THEN 1
         ELSE 0
         END
         - CASE DATEDIFF(DAY, '1/1/1900', @PPDtEnd) % 7
         WHEN 5 THEN 1
         ELSE 0
         END
     AS NumberOfWeekdays


PS: I sent all the above before realize you'd included an image of your results. I ran my code using the dates in the last line and indeed the original code showed 4, but the code I added showed 5. So you can just use that if you want. But feel free to reply if you have followup questions/comments

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.