Group by problem

Question

Post reply

Group by problem

geckonido

Grasshopper

Points: 12
More actions
March 23, 2011 at 12:49 pm

#233765

Hello, I need to group a set of data like this:
Name Date Work
----- ----------- ------------
John, 2008-01-01, Work type A
John, 2008-02-01, Work type A
John, 2008-03-01, Work type A
John, 2008-04-01, Work type A
John, 2008-05-01, Work type B
John, 2008-06-01, Work type B
John, 2008-07-01, Work type A
John, 2008-08-01, Work type A
John, 2008-09-01, Work type B
John, 2008-10-01, Work type B
John, 2008-11-01, Work type A
John, 2008-12-01, Work type B
And obtaina an result like this:
Name Initial-date End-date Work
----- ----------- ----------- ------------
John, 2008-01-01, 2008-04-01, Work type A
John, 2008-05-01, 2008-06-01, Work type B
John, 2008-07-01, 2008-08-01, Work type A
John, 2008-09-01, 2008-10-01, Work type B
John, 2008-11-01, 2008-11-01, Work type A
John, 2008-12-01, 2008-12-01, Work type B
When i try to group an find MIN and MAX by Work field the result is:
John, 2008-01-01, 2008-11-01, Work type A
John, 2008-05-01, 2008-12-01, Work type B
avoiding all records in then middle... ¿How can i do?
Thanks !!!!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

I looks like you want to SELECT the min and max dates (and Name and Work), and GROUP BY the Name and Work.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

WayneS SSC Guru Points: 95461 More actions · Answer 2

How's this?

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test TABLE (Name CHAR(4), Date DATE, Work CHAR(11));

INSERT INTO @test

SELECT 'John', '2008-01-01', 'Work type A' UNION ALL

SELECT 'John', '2008-02-01', 'Work type A' UNION ALL

SELECT 'John', '2008-03-01', 'Work type A' UNION ALL

SELECT 'John', '2008-04-01', 'Work type A' UNION ALL

SELECT 'John', '2008-05-01', 'Work type B' UNION ALL

SELECT 'John', '2008-06-01', 'Work type B' UNION ALL

SELECT 'John', '2008-07-01', 'Work type A' UNION ALL

SELECT 'John', '2008-08-01', 'Work type A' UNION ALL

SELECT 'John', '2008-09-01', 'Work type B' UNION ALL

SELECT 'John', '2008-10-01', 'Work type B' UNION ALL

SELECT 'John', '2008-11-01', 'Work type A' UNION ALL

SELECT 'John', '2008-12-01', 'Work type B';

;

WITH cte AS

(

-- add a Grp column as the difference of the row number by name/date and

-- the row number when restarted when name/work changes.

SELECT Name,

Date,

Work,

Grp = ROW_NUMBER() OVER (ORDER BY Name, Date) -

ROW_NUMBER() OVER (PARTITION BY Name, Work ORDER BY Date)

FROM @test

), CTE2 AS

(

-- get the additional columns you want

SELECT Name,

[Initial Date] = MIN(Date),

[End Date] = MAX(Date),

Work

FROM cte

GROUP BY Grp, name, work

)

-- display the results, sorted by the name/date

SELECT *

FROM CTE2

ORDER BY Name, [Initial Date];

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

geckonido Grasshopper Points: 12 More actions · Answer 3

It's works very fine !!!!!

Now, i will try to make a procedure to apply your solution to the problematic table !!!

THANK YOU !!!!!!!!!!!