Group ranges of dates

Question

I have the following table:

CREATE TABLE Contracts
(CT_ID INT NOT NULL,
CT_From SMALLDATETIME NOT NULL,
CT_Till SMALLDATETIME NULL,
CT_Hours TINYINT NOT NULL
CT_EmployeeID INT NOT NULL)

I have the following test data

Contracts				
				
ID	From		Till		Hours	EmployeeID
				
1	2009-01-01			19	1
2	2009-03-01	2009-06-16	8	1
3	2009-05-01	2009-07-30	4	1
4	2009-06-01			19	2
5	2009-07-01	2009-07-31	8	2

And I'm looking for the following result:

From		Till		Hours	EmployeeID
			
2009-01-01	2009-02-28	19	1
2009-03-01	2009-04-30	27	1
2009-05-01	2009-06-16	31	1
2009-06-17	2009-07-30	23	1
2009-08-01			19	1
2009-06-01	2009-06-30	19	2
2009-07-01	2009-07-31	8	2
2009-08-01			19	2

How can I get this result?  Is this possible in a query or through the use of cursor?  Calendar table?

Test data script:

insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (1, 39814, null, 19, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (2, 39873, 39980, 8, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (3, 39934, 40024, 4, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (4, 39965, null, 19, 2)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (5, 39995, 40025, 8, 2)

Thanks in advance!

Answer 1

can you explain how do you get the value for column Hours  ?

And for your expected result, row no 5, there is a From date of '2009-08-01' where does it comes from ?

---

KH Tan

Answer 2

Thanks for your reply.

I did indeed need to provide some more info:

The hours is the sum of all contracts for that employee during that time period.  An employee can have multiple contracts running at the same time with different start and end dates.  I need to have the summed up result.

You are correct: row 5 is faulty, the begin date should be 2009-07-31 and not 2009-08-01.

Answer 3

Thanks for your reply.

I did indeed need to provide some more info:

The hours is the sum of all contracts for that employee during that time period.  An employee can have multiple contracts running at the same time with different start and end dates.  I need to have the summed up result.

You are correct: row 5 is faulty, the begin date should be 2009-07-31 and not 2009-08-01.

Let's start over altogether.  Your example does not match the sample data insert scripts.  Here is the real data using your insert scripts.  What is the expected output?

CT_ID  CT_From               CT_Till              CT_Hours   CT_EmployeeID
1      2009-01-03 00:00:00   NULL                 19         1
2      2009-03-03 00:00:00   2009-06-18 00:00:00  8          1
3      2009-05-03 00:00:00   2009-08-01 00:00:00  4          1
4      2009-06-03 00:00:00   NULL                 19         2
5      2009-07-03 00:00:00   2009-08-02 00:00:00  8          2

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 4

Ok, this should be the expected output:

From Till Hours EmployeeID

From		Till		Hours	EmployeeID
			
2009-01-03	2009-03-02	19	1
2009-03-03	2009-05-02	27	1
2009-05-03	2009-06-18	31	1
2009-06-19	2009-08-01	23	1
2009-08-02			19	1
2009-06-03	2009-07-02	19	2
2009-07-03	2009-08-02	8	2
2009-08-03			19	2

Answer 5

Can you explain how the 27 hours for row 2 comes from ?

If it is cumulative hours of Employee 1 for 19 hours (2009-01-03 to 2009-03-02) + 8 hours (2009-03-03 to 2009-05-02) ?
But in your sample data row 2, the 8 hours is for 2009-03-01 to 2009-06-16. What happen to the hours between 2009-05-03 to 2009-06-16 ?

Furthermore, row 3 of the result is additional 4 hours (31 - 27) for 2009-05-03 to 2009-06-18. The 4 hours is coming from ID 3 of your sample data ? Where is the date 06-18 comes from ? Where is the ending date of 2009-06-16

I think there are still some miss-match between the sample data and your expected result. Can you re-post the sample data together with the expected result. And kindly explain how do you derive the FROM and TO date as well as the Hours for each line of the result ?

---

KH Tan

Answer 6

Thanks for your reply again.

Let's concentrate on EmployeeID 1.

He has 3 contracts which overlap sometimes.

First contracts begins on 2009-01-03 and doesn't end.
When you look at 2009-01-03 there are no other contracts on that date for that employee so the first line is pretty easy:

2009-01-03 till 2009-03-02 for 19 hours.

The 2009-03-02 comes from the fact that on 2009-03-03 a new contract is started so the previous needs to be stopped.

Contract 3 goes till 06-18 and that is the reason why it is in the Till.  I checked again and I don't believe there is a mismatch.

CREATE TABLE Contracts
(CT_ID INT NOT NULL,
CT_From SMALLDATETIME NOT NULL,
CT_Till SMALLDATETIME NULL,
CT_Hours TINYINT NOT NULL,
CT_EmployeeID INT NOT NULL)

insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (1, 39814, null, 19, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (2, 39873, 39980, 8, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (3, 39934, 40024, 4, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (4, 39965, null, 19, 2)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (5, 39995, 40025, 8, 2)

Since a picture speaks a thousand words, I have drawn it out, I think it will be much easier to understand that way.

See here: http://www.entrysoft.com/DateRange.jpg

Answer 7

oh ok. Now i got it. I wasn't looking at your insert statement. I can't translate numbers into date just like that. I was looking at your sample data you posted in your 1st thread. I must have missed Phil's comment on the sample data also.

---

KH Tan

Answer 8

the hours part is quite tricky .  . . have not figure out that part yet. Maybe this could give you or somebody else a lead

; with CT_FT
as
(
    select    ft.CT_From, ft.CT_Till, ft.CT_EmployeeID, ft.CT_Hours
    from    (
            select    CT_From, CT_Till, CT_EmployeeID, CT_Hours
            from    Contracts
            union
            select    CT_Till + 1, NULL, CT_EmployeeID, CT_Hours
            from    Contracts
            where    CT_Till    is not null
        ) ft
)
select    CT_From        = f.CT_From,
    CT_Till     = coalesce(t.CT_Till, f.CT_Till),
    CT_EmployeeID    = f.CT_EmployeeID
from    CT_FT f
    cross apply
    (
        select    CT_Till = min(CT_Till)
        from
        (
            select    min(CT_From) - 1 as CT_Till
            from    CT_FT x
            where    x.CT_EmployeeID    = f.CT_EmployeeID
            and    x.CT_From    > f.CT_From

            union

            select    min(CT_Till) as CT_Till
            from    CT_FT x
            where    x.CT_EmployeeID    = f.CT_EmployeeID
            and    x.CT_Till    > f.CT_From
        ) t
    ) t
order by f.CT_EmployeeID, f.CT_From

/*
CT_From     CT_Till     CT_EmployeeID
----------- ----------- -------------
2009-01-03  2009-03-02  1
2009-03-03  2009-05-02  1
2009-05-03  2009-06-18  1
2009-06-19  2009-08-01  1
2009-08-02  NULL        1
2009-06-03  2009-07-02  2
2009-07-03  2009-08-02  2
2009-08-03  NULL        2

(8 row(s) affected)
*/

---

KH Tan

Answer 9

Thanks KH Tan,

Gonna look into that, seems very nice already!

Answer 10

Is result correct ?

; with CT_FT
as
(
    select    ft.CT_From, ft.CT_Till, ft.CT_EmployeeID, ft.CT_Hours
        from   
    (
                select    CT_From, CT_Till, CT_EmployeeID, CT_Hours
                from    Contracts

                union

                select    CT_Till + 1, NULL, CT_EmployeeID, CT_Hours = 0
                from    Contracts
                where   CT_Till    is not null
        ) ft
)
select  CT_From            = f.CT_From,
        CT_Till         = coalesce(t.CT_Till, f.CT_Till),
    CT_Hours    = h.CT_Hours,
        CT_EmployeeID   = f.CT_EmployeeID
from    CT_FT f
        cross apply
        (
            select    CT_Till = min(CT_Till)
            from
            (
                    select  min(CT_From) - 1 as CT_Till
                    from    CT_FT x
                    where   x.CT_EmployeeID    = f.CT_EmployeeID
                    and        x.CT_From    > f.CT_From

            union

                    select  min(CT_Till) as CT_Till
                    from    CT_FT x
                    where   x.CT_EmployeeID = f.CT_EmployeeID
                    and        x.CT_Till        > f.CT_From
            ) t
        ) t
    cross apply
    (
        select    CT_Hours = sum(CT_Hours)
        from    Contracts x
        where    x.CT_EmployeeID    = f.CT_EmployeeID
        and    x.CT_From    <= f.CT_From
        and    (   
                x.CT_Till    >= f.CT_From
            or    x.CT_Till    is null
            )
    ) h
order by f.CT_EmployeeID, f.CT_From

/*
CT_From     CT_Till     CT_Hours    CT_EmployeeID
----------- ----------- ----------- -------------
2009-01-03  2009-03-02  19          1
2009-03-03  2009-05-02  27          1
2009-05-03  2009-06-18  31          1
2009-06-19  2009-08-01  23          1
2009-08-02  NULL        19          1
2009-06-03  2009-07-02  19          2
2009-07-03  2009-08-02  27          2
2009-08-03  NULL        19          2

(8 row(s) affected)
*/

---

KH Tan

Answer 11

Hi KH Tan,

It seems ALMOST correct.  With the suggested testing rows it works.  However sometimes there are duplicates created!

If you use this test data:

insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (1, '2009-06-01', null, 19, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (2, '2009-03-01', '2009-05-31', 8, 1)

The result should be the exact same lines because there is no overlapping, however when you run your query you get 3 lines.

Answer 12

Hi,


Try this
CREATE TABLE Contracts
(CT_ID INT NOT NULL,
CT_From SMALLDATETIME NOT NULL,
CT_Till SMALLDATETIME NULL,
CT_Hours TINYINT NOT NULL,
CT_EmployeeID INT NOT NULL)

insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (1, 39814, null, 19, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (2, 39873, 39980, 8, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (3, 39934, 40024, 4, 1)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (4, 39965, null, 19, 2)
insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (5, 39995, 40025, 8, 2)

select * , coalesce(CT_Till,
(Select c.CT_From - 1 from contracts c where contracts.CT_ID + 1 = c.CT_ID ))
from contracts

drop table contracts


it will return your required result.

---

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

Answer 13

Hi Shamas,

Thanks for your reply.  Sorry that query doens't return the required results. 

Answer 14

change this part. Add another where condition to the 2nd query of the UNION

; with CT_FT
as
(
    select    ft.CT_From, ft.CT_Till, ft.CT_EmployeeID, ft.CT_Hours
    from    (
            select    CT_From, CT_Till, CT_EmployeeID, CT_Hours
            from    Contracts
            union
            select    CT_Till + 1, NULL, CT_EmployeeID, CT_Hours
            from    Contracts c
            where    CT_Till    is not null
        and     not exists (select * from Contracts x where x.CT_From = c.CT_Till + 1)
        ) ft
)

---

KH Tan

Answer 15

Hi K H Tan,

Thanks, this looks perfect.  One question:


        and     not exists (select * from Contracts x where x.CT_From = c.CT_Till + 1)

Doesn't this part need to be:

        and     not exists (select * from Contracts x where x.CT_From = c.CT_Till + 1 and x.CT_EmployeeID = c.CT_EmployeeID)

?

Answer 16

yes. You are correct. I missed that

---

KH Tan

Answer 17

Hi KH Tan,

Works nicely, there is only one more bug I discovered.

If you have one single line as a contract:

delete from contracts

insert into Contracts (CT_ID, CT_From, CT_Till, CT_Hours, CT_EmployeeID) values (2, 39873, 39980, 8, 1)

Then the result will give 2 rows instead of one.  Not a very big issue because the Hours will be null so we can filter them out that way.