Pulling dates out of a date range

[oneilltnc]

I have a problem where our system stores employee vacations with only a start date and end date. So if an employee takes off for five days I only have two fields to work with, start date and end date. The problem is I need to look at each date within this range to determine by day of week what they should be paid. Does anyone have any ideas on how to acheive this.

I tried taking the DateDiff to determine the number of days for the date range, but an employee can be paid different amounts for each day so this won't work.

 

[jhall01]

Could you make a table of every date in the year and use join on to that using your begin and end date to get back the actual dates?

table1
employee begdate enddate
jondoe 1/2/2005 1/5/2005

easy way to generate table2 is use excel to drag out the dates then import into SQL

table2
datelist
1/1/2005
1/2/2005
1/3/2005
1/4/2005
1/5/2005
...
12/31/2005

[blue]SELECT[/blue]
  a.employee, b.datelist
[blue]FROM[/blue]
  table1 a
  [blue]JOIN[/blue]
  table2 b
  [blue]on[/blue]
  b.datelist between a.begdate and a.enddate

 

[maswien]

Table emp: emp_id, start_date, end_date

try this:


select datediff(dd, start_date, end_date) -
(case when datepart(dw, start_date) = 2
then datediff(dd, start_date, end_date)/6 +
datediff(dd, start_date, end_date)/7
when datepart(dw, start_date) = 3
then (datediff(dd, start_date, end_date)+1)/6 +
(datediff(dd, start_date, end_date)+1)/7
when datepart(dw, start_date) = 4
then (datediff(dd, start_date, end_date)+2)/6 +
(datediff(dd, start_date, end_date)+2)/7
when datepart(dw, start_date) = 5
then (datediff(dd, start_date, end_date)+3)/6 +
(datediff(dd, start_date, end_date)+3)/7
when datepart(dw, start_date) = 6
then (datediff(dd, start_date, end_date)+4)/6 +
(datediff(dd, start_date, end_date)+4)/7
when datepart(dw, start_date) = 7
then (datediff(dd, start_date, end_date)+5)/6 +
(datediff(dd, start_date, end_date)+5)/7
end ) as number_of_paid_days
from emp

 

[maswien]

I assume that there is no sense for a start_date is sunday.

The simplified version:


select datediff(dd, start_date, end_date) -
(
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/6 +
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/6
)
as number_of_paid_days
from emp

 

[maswien]

typo, it should be

select datediff(dd, start_date, end_date) -
(
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/6 +
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/7
)
as number_of_paid_days
from emp

 

[r937]

if christmas is a tuesday, mjia, and if employees don't get paid for christmas, then if they are absent from the 23rd to the 28th, your formula will count christmas

the table of dates is by far the better solution for this sort of situation, because it's never as simple as counting monday through friday only

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[maswien]

Thanks r937, good point, I didn't think of that!

Then we need a lookup table HOLIDAYS which record all the holidays between min(start_date) and today,

theDay
=====
...
2004-12-25
...
2005-01-01
...

So the SQL should be:



select datediff(dd, start_date, end_date) -
(
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/6 +
(datediff(dd, start_date, end_date)+
datepart(dw, start_date) - 2 )/7
) -
( select count(*) from HOLIDAYS
where theDay between e1.start_date
and e1.end_date
)
as number_of_paid_days
from emp e1

 

[maswien]

still wrong, the lookup table need include both th holidays and week ends, so the SQL should be:


select datediff(dd, start_date, end_date) -
( select count(*) from HOLIDAYS
where theDay between e1.start_date
and e1.end_date
)
as number_of_paid_days
from emp e1

 

[r937]

well, i prefer one row in the calendar table for every date, with a column precalculated for the weekday (e.g. 0=sunday,6=saturday) and another column to indicate the hoiliday

then a simple join gets the answer

select e1.empname
     , count(*) as number_of_paid_days
  from emp e1
inner
  join calendar
    on calendar.thedate
       between e1.start_date 
           and e1.end_date        
   and calendar.weekday 
       between 1 and 5
   and calendar.holiday is null
group
    by e1.empname

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

Rudy, that can get confusing because the system function DatePart(Weekday,GetDate()) returns 1 (Sunday) through 7 (Saturday). I would conform to their standard. Minor but helps us simple minded folks.
Of course there's always the issue of the DateFirst setting which can alter the definition from the default installation (Sunday=1).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

just trying to see if everybody is awake



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)