Select record from group

  • I was assigned a difficult project. It is beyond by skill. I need expert to help me.

    From sample data,

    1) Group by ID and COLOR

    2) From this group, select ID, min(DATEFROM),max(DATETO) and Color and then insert into a new table,

    3) For example, goup 111 and blue, select min(DATEFROM)=01/10/2012 and max(DATETO)=03/16/2012

    4) Keep on selecting until end file.

    Here is sample data:

    IDDATEFROMDATETOCOLOR

    11101/10/201201/11/2012BLUE

    11102/02/201202/02/2012BLUE

    11103/15/201203/16/2012BLUE

    11104/05/201204/05/2012GREEN

    11105/25/201205/26/2012GREEN

    11106/06/201206/16/2012GREEN

    11107/17/201207/17/2012BLUE

    11109/08/201210/10/2012BLUE

    22201/10/201301/11/2013RED

    22202/02/201302/02/2013RED

    22203/15/201303/16/2013RED

    22204/05/201304/05/2013GREEN

    22205/25/201305/26/2013GREEN

    22206/06/201306/16/2013GREEN

    22207/17/201307/17/2013BLUE

    22209/08/201310/10/2013BLUE

    Finally, I need select records and insert a new table like below:

    11101/10/201203/16/2012BLUE

    11104/05/201206/16/2012GREEN

    11107/17/201210/10/2012BLUE

    22201/10/201303/16/2013RED

    22204/05/201306/16/2013GREEN

    22207/17/201310/10/2013BLUE

  • You don't need a bunch of steps for this. A simple query (that you pretty much wrote) will do this in a single step.

    select ID, min(DATEFROM),max(DATETO),Color

    group by ID, Color

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • An 'insert into' and the new table name will create the table from the results.

    insert into newtable

    select ID, min(DATEFROM),max(DATETO),Color

    group by ID, Color

  • No, this result is not right.

    If I use this query, the result is not same as I expected.

  • Oops, that will insert records into an existing table. Try:

    select ID, min(DATEFROM) as date1, max(DATETO)as date2,Color

    into newtable from originaltable

    group by ID, Color

  • adonetok (9/12/2013)


    No, this result is not right.

    If I use this query, the result is not same as I expected.

    Then maybe you should post ddl, sample data and desired output so we can help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • How to post ddl?

    I list the sample data and result I need again as below.

    Sample data:

    ID--DATEFROM-DATETO-----COLOR

    --------------------------------

    111 01/10/2012 01/11/2012 BLUE

    111 02/02/2012 02/02/2012 BLUE

    111 03/15/2012 03/16/2012 BLUE

    111 04/05/2012 04/05/2012 GREEN

    111 05/25/2012 05/26/2012 GREEN

    111 06/06/2012 06/16/2012 GREEN

    111 07/17/2012 07/17/2012 BLUE

    111 09/08/2012 10/10/2012 BLUE

    222 01/10/2013 01/11/2013 RED

    222 02/02/2013 02/02/2013 RED

    222 03/15/2013 03/16/2013 RED

    222 04/05/2013 04/05/2013 GREEN

    222 05/25/2013 05/26/2013 GREEN

    222 06/06/2013 06/16/2013 GREEN

    222 07/17/2013 07/17/2013 BLUE

    222 09/08/2013 10/10/2013 BLUE

    Result:

    ID--DATEFROM-DATETO-----COLOR

    --------------------------------

    111 01/10/2012 03/16/2012 BLUE

    111 04/05/2012 06/16/2012 GREEN

    111 07/17/2012 10/10/2012 BLUE

    222 01/10/2013 03/16/2013 RED

    222 04/05/2013 06/16/2013 GREEN

    222 07/17/2013 10/10/2013 BLUE

  • adonetok (9/12/2013)


    How to post ddl?

    I list the sample data and result I need again as below.

    Come on...this isn't the first time we have asked you for ddl and sample data. You know that you need to post create table statements and insert statements for data. Just sticking a bunch of characters does not make it easy for the volunteers around here to help.

    Please take a few minutes and read the first article in my signature. This is nowhere near the first time you have been told this. Your query is not all that difficult but I really don't feel like spending 20-30 minutes creating the tables and data so I can then spend time working on the actual problem. You have to do some of the work, after all you are the one who is getting ALL of the pay for the job.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I might point out that your desired output is ambiguous. you have a 111/Blue group twice in your suggested output. What are your criteria for having 2 in that?

  • That is what boss whant.

    The sample data sort by DATEFROM but once COLOR change, it need to re-group and need min(DATEFROM) adn max(DATETO) again.

  • venoym (9/12/2013)


    I might point out that your desired output is ambiguous. you have a 111/Blue group twice in your suggested output. What are your criteria for having 2 in that?

    I'm guessing, based on the desired output, that the second group should have been a 111/Red group. If this is the case, please see below for one possible solution:

    --==== CREATE A TEMP TABLE TO HOLD THIS COLOR HISTORY DATA

    if OBJECT_ID('tempdb..#colorHistory', 'u') is not null drop table #colorHistory

    create table #colorHistory (id varchar(3), dateFrom date, dateTo date, color varchar(5))

    insert #colorHistory

    values

    ('111', '20120110', '20120111', 'blue'),

    ('111', '20120202', '20120202', 'blue'),

    ('111', '20120315', '20120316', 'blue'),

    ('111', '20120405', '20120405', 'green'),

    ('111', '20120526', '20120526', 'green'),

    ('111', '20120616', '20120616', 'green'),

    ('111', '20120717', '20120717', 'red'), --corrected this from blue

    ('111', '20121010', '20121010', 'red'), --corrected this from blue

    ('222', '20130110', '20130110', 'red'),

    ('222', '20130202', '20130202', 'red'),

    ('222', '20130316', '20130316', 'red'),

    ('222', '20130405', '20130405', 'green'),

    ('222', '20130526', '20130526', 'green'),

    ('222', '20130616', '20130616', 'green'),

    ('222', '20130717', '20130712', 'blue')

    --==== NOW GET THE MIN DATEFROM AND MAX DATETO FOR EACH COLOR/ID COMBINATION

    selectc1.id

    ,MIN(c1.dateFrom)

    ,MAX(c1.dateTo)

    ,c1.color

    from #colorHistory c1

    group by c1.id, c1.color

    order by c1.id, c1.color

    This is essentially the same solution that the original answers were suggesting. Can you help us understand why this is not the appropriate solution?

    There are also overblown solutions involving CTEs and row_number()... but I'm sure you're not interested in that.

    Hope this helps.

    PS:

    Sean Lange (9/12/2013)


    You have to do some of the work, after all you are the one who is getting ALL of the pay for the job.

    Sean is right, if I wasn't bored in a meeting, I wouldn't have bothered with creating the table and data here. If you expect people to help you, you need to at least do a little of the legwork.

  • Here is my ddl. Please help.

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (

    [AUTOID] INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,

    [ID] [varchar](50) NULL,

    [DATEFROM] [datetime] NULL,

    [DATETO] [datetime] NULL,

    [varchar](255) NULL

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== Insert the test data into the test table

    INSERT INTO #mytable

    (ID, DATEFROM, DATETO,COLOR)

    SELECT '111','Jan 10 2012 12:00AM','Jan 11 2012 12:00AM','BLUE' UNION ALL

    SELECT '111','Feb 2 2012 12:00AM','Feb 2 2012 12:00AM','BLUE' UNION ALL

    SELECT '111','Mar 15 2012 12:00AM','Mar 16 2012 12:00AM','BLUE' UNION ALL

    SELECT '111','Apr 5 2012 12:00AM','Apr 5 2012 12:00AM','GREEN' UNION ALL

    SELECT '111','May 25 2012 12:00AM','May 26 2012 12:00AM','GREEN' UNION ALL

    SELECT '111','Jun 6 2012 12:00AM','Jun 16 2012 12:00AM','GREEN' UNION ALL

    SELECT '111','Jul 17 2012 12:00AM','Jul 17 2012 12:00AM','BLUE' UNION ALL

    SELECT '111','Sep 8 2012 12:00AM','Oct 10 2012 12:00AM','BLUE' UNION ALL

    SELECT '222','Jan 10 2013 12:00AM','Jan 11 2013 12:00AM','RED' UNION ALL

    SELECT '222','Feb 2 2013 12:00AM','Feb 2 2013 12:00AM','RED' UNION ALL

    SELECT '222','Mar 15 2013 12:00AM','Mar 16 2013 12:00AM','RED' UNION ALL

    SELECT '222','Apr 5 2013 12:00AM','Apr 5 2013 12:00AM','GREEN' UNION ALL

    SELECT '222','May 25 2013 12:00AM','May 26 2013 12:00AM','GREEN' UNION ALL

    SELECT '222','Jun 6 2013 12:00AM','Jun 16 2013 12:00AM','GREEN' UNION ALL

    SELECT '222','Jul 17 2013 12:00AM','Jul 17 2013 12:00AM','BLUE' UNION ALL

    SELECT '222','Sep 8 2013 12:00AM','Oct 10 2013 12:00AM','BLUE' UNION ALL

    SELECT '111','Jan 10 2012 12:00AM','Mar 16 2012 12:00AM','BLUE' UNION ALL

    SELECT '111','Apr 5 2012 12:00AM','Jun 16 2012 12:00AM','GREEN' UNION ALL

    SELECT '111','Jul 17 2012 12:00AM','Oct 10 2012 12:00AM','BLUE' UNION ALL

    SELECT '222','Jan 10 2013 12:00AM','Mar 16 2013 12:00AM','RED' UNION ALL

    SELECT '222','Apr 5 2013 12:00AM','Jun 16 2013 12:00AM','GREEN' UNION ALL

    SELECT '222','Jul 17 2013 12:00AM','Oct 10 2013 12:00AM','BLUE'

  • I see the hitch up here...you need to see the min date and max date from each of the consecutive color/ID group pairings.

    Originally, we had been treating the grouping value (business key) as the pairing of color and ID. The key is actually the pairing of color and ID over sequential, uninterrupted AUTOID values.

    In this case, the simple solution that the group has previously been advocating will not work. At first glance, this is probably going to require either really complicated logic or row by row processing. I'll keep considering the "complicated logic" approach (as to not anger Lord Moden), and report back if I come up with anything.

  • paulcauchon (9/12/2013)


    I see the hitch up here...you need to see the min date and max date from each of the consecutive color/ID group pairings.

    Originally, we had been treating the grouping value (business key) as the pairing of color and ID. The key is actually the pairing of color and ID over sequential, uninterrupted AUTOID values.

    In this case, the simple solution that the group has previously been advocating will not work. At first glance, this is probably going to require either really complicated logic or row by row processing. I'll keep considering the "complicated logic" approach (as to not anger Lord Moden), and report back if I come up with anything.

    Not that complicated;

    SELECT ID, DATEFROM = MIN(DATEFROM), DATETO = MAX(DATETO), COLOR

    FROM (

    SELECT autoid, ID, DATEFROM, DATETO, COLOR,

    NewGroup = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COLOR) - AUTOID

    FROM #mytable

    ) d

    GROUP BY ID, COLOR, NewGroup

    ORDER BY ID, MIN(DATEFROM)


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Thank you very much.

    This is exatly what I need.

    The last question is how to delete duplicate records before output?

    ID-------DATEFROM-----------------DATETO--------------------COLOR

    -----------------------------------------------------------------------

    1112012-01-10 00:00:00.0002012-03-16 00:00:00.000BLUE

    1112012-01-10 00:00:00.0002012-03-16 00:00:00.000BLUE

    1112012-04-05 00:00:00.0002012-06-16 00:00:00.000GREEN

    1112012-04-05 00:00:00.0002012-06-16 00:00:00.000GREEN

    1112012-07-17 00:00:00.0002012-10-10 00:00:00.000BLUE

    1112012-07-17 00:00:00.0002012-10-10 00:00:00.000BLUE

    2222013-01-10 00:00:00.0002013-03-16 00:00:00.000RED

    2222013-01-10 00:00:00.0002013-03-16 00:00:00.000RED

    2222013-04-05 00:00:00.0002013-06-16 00:00:00.000GREEN

    2222013-04-05 00:00:00.0002013-06-16 00:00:00.000GREEN

    2222013-07-17 00:00:00.0002013-10-10 00:00:00.000BLUE

    2222013-07-17 00:00:00.0002013-10-10 00:00:00.000BLUE

Viewing 15 posts - 1 through 15 (of 22 total)

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