CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
whywake
whywake

Reputation: 910

To club the rows for week days

I have data like below:

StartDate     EndDate    Duration 
----------
41890         41892       3
41898         41900       3
41906         41907       2
41910         41910       1

StartDate and EndDate are respective ID values for any dates from calendar. I want to calculate the sum of duration for consecutive days. Here I want to include the days which are weekends. E.g. in the above data, let's say 41908 and 41909 are weekends, then my required result set should look like below.

I already have another proc that can return me the next working day, i.e. if I pass 41907 or 41908 or 41909 as DateID in that proc, it will return 41910 as the next working day. Basically I want to check if the DateID returned by my proc when I pass the above EndDateID is same as the next StartDateID from above data, then both the rows should be clubbed. Below is the data I want to get.

ID          StartDate     EndDate    Duration 
----------
278457        41890       41892       3
278457        41898       41900       3
278457        41906       41910       3

Please let me know in case the requirement is not clear, I can explain further.

My Date Table is like below:

DateId        Date      Day
----------
41906      09-04-2014    Thursday
41907      09-05-2014    Friday
41908      09-06-2014    Saturdat
41909      09-07-2014    Sunday
41910      09-08-2014    Monday

Here is the SQL Code for setup:

CREATE TABLE Table1
(
StartDate INT,
EndDate INT,
LeaveDuration INT
)

INSERT INTO Table1
VALUES(41890, 41892, 3),
(41898, 41900, 3),
(41906, 41907, 3),
(41910, 41910, 1)

CREATE TABLE DateTable
(
DateID INT,
Date DATETIME,
Day VARCHAR(20)
)

INSERT INTO DateTable
VALUES(41907, '09-05-2014', 'Friday'),
(41908, '09-06-2014', 'Saturday'),
(41909, '09-07-2014', 'Sunday'),
(41910, '09-08-2014', 'Monday'),
(41911, '09-09-2014', 'Tuesday')

Upvotes: 2

Views: 167

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

This is rather complicated. Here is an approach using window functions.

First, use the date table to enumerate the dates without weekends (you can also take out holidays if you want). Then, expand the periods into one day per row, by using a non-equijoin.

You can then use a trick to identify sequential days. This trick is to generate a sequential number for each id and subtract it from the sequential number for the dates. This is a constant for sequential days. The final step is simply an aggregation.

The resulting query is something like this:

with d as (
      select d.*, row_number() over (order by date) as seqnum
      from dates d
      where day not in ('Saturday', 'Sunday')
     )
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
             (d.seqnum - row_number() over (partition by id order by ds.date) ) as grp
      from table t join
           d ds
           on ds.date between t.startdate and t.enddate
     ) t
group by t.id, grp;

EDIT:

The following is the version on this SQL Fiddle:

with d as (
      select d.*, row_number() over (order by date) as seqnum
      from datetable d
      where day not in ('Saturday', 'Sunday')
     )
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
             (ds.seqnum - row_number() over (partition by id order by ds.date) ) as grp
      from (select t.*, 'abc' as id from table1 t) t join
           d ds
           on ds.dateid between t.startdate and t.enddate
     ) t
group by grp;

I believe this is working, but the date table doesn't have all the dates in it.

Upvotes: 2

Related Questions