Grouping Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Issue

Author

Topic

klanda20
Starting Member

5 Posts

Posted - 2011-10-12 : 15:47:17

Hello All,

I have a problem that is stumping me. I have records that have unique ID's and a start date and an end date. If a record has an end date that equals another record's start date I need to role that up into 1 "encounter".

Basically the data looks like this....

ID | start | end
4 01/02/2011 01/05/2011
4 01/05/2011 01/11/2011
4 03/03/2011 03/04/2011
4 03/04/2011 03/06/2011

So I need to somehow group distinguish group rows 1&2 from rows 3&4.

Sorry if that it confusing....it's somewhat hard to verbalize.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-12 : 17:23:03

Does this help?

DECLARE @Foo TABLE (ID INT, start DATE, [end] DATE)

INSERT @Foo
VALUES
(4, '01/02/2011', '01/05/2011'),
(4, '01/05/2011', '01/11/2011'),
(4, '03/03/2011', '03/04/2011'),
(4, '03/04/2011', '03/06/2011')

SELECT *
FROM @Foo AS A
INNER JOIN @Foo AS B
ON A.ID = B.ID
AND A.[end] = B.start

klanda20
Starting Member

5 Posts

Posted - 2011-10-13 : 07:40:58

Lamprey,

Thanks that does work in that scenario but I should have expanded my explanation. There can be multiple records that should roll up into one. See the example below.

DECLARE @Foo TABLE (rn INT, ID INT, start DATE, [end] DATE)

INSERT @Foo
VALUES
(1, 4, '01/02/2011', '01/05/2011'),
(2, 4, '01/05/2011', '01/11/2011'),
(3, 4, '03/03/2011', '03/04/2011'),
(4, 4, '03/04/2011', '03/06/2011'),
(5, 4, '01/11/2011', '01/21/2011')

SELECT *
FROM @Foo AS A
INNER JOIN @Foo AS B
ON A.ID = B.ID
AND A.[end] = B.start

In this example row number 1,2 and 5 should roll up and row number 3 and 4 should roll up.

Thanks again!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-13 : 07:52:59

Can you show us what do you think how the output should look like?

No, you're never too old to Yak'n'Roll if you're too young to die.

klanda20
Starting Member

5 Posts

Posted - 2011-10-13 : 08:10:25

Ultimately I would like to update the table and flag each group as so....

rn id start end group
1 4 2011-01-02 2011-01-05 1
2 4 2011-01-05 2011-01-11 1
3 4 2011-03-03 2011-03-04 2
4 4 2011-03-04 2011-03-06 2
5 4 2011-01-11 2011-01-21 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-13 : 12:54:02

I didn't do the update, but this is one what that you might be able to group your rows (obviously replace * with column names):

DECLARE @Foo TABLE (rn INT, ID INT, start DATE, [end] DATE)

INSERT @Foo
VALUES
(1, 4, '01/02/2011', '01/05/2011'),
(2, 4, '01/05/2011', '01/11/2011'),
(3, 4, '03/03/2011', '03/04/2011'),
(4, 4, '03/04/2011', '03/06/2011'),
(5, 4, '01/11/2011', '01/21/2011')

;
WITH cte AS
(
SELECT 
    A.*, A.rn AS ParentGroup, 1 AS Level
FROM 
    @Foo AS A
WHERE
    NOT EXISTS    
    ( 
        SELECT *
        FROM @Foo AS B
        WHERE
            A.ID = B.ID
            AND A.start = B.[end]
    )
UNION ALL
    SELECT 
        B.*, A.ParentGroup, A.Level + 1
    FROM 
        @Foo AS B
    INNER JOIN 
        cte AS A
        ON B.ID = A.ID
        AND B.start = A.[end]
)
SELECT *
FROM cte

EDIT: Here is another way using a left join instead of a NOT EXISTS, might be faster..?

DECLARE @Foo TABLE (rn INT, ID INT, start DATE, [end] DATE)

INSERT @Foo
VALUES
(1, 4, '01/02/2011', '01/05/2011'),
(2, 4, '01/05/2011', '01/11/2011'),
(3, 4, '03/03/2011', '03/04/2011'),
(4, 4, '03/04/2011', '03/06/2011'),
(5, 4, '01/11/2011', '01/21/2011')
;
WITH cte AS
(
    SELECT 
      A.*, A.rn AS ParentGroup, 1 AS Level
    FROM 
        @Foo AS A
    LEFT OUTER JOIN
        @Foo AS B
        ON A.ID = B.ID
        AND A.start = B.[end]

UNION ALL

    SELECT 
        B.*, A.ParentGroup, A.Level + 1
    FROM 
        @Foo AS B
    INNER JOIN 
        cte AS A
        ON B.ID = A.ID
        AND B.start = A.[end]
)
SELECT *
FROM cte

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources