Post reply

Help with query that group consecutive records based on a field value

  • November 16, 2015 at 8:26 am

    #399330

    I have a table like this

    CREATE TABLE temporal(serial varchar(50), modelo varchar(50),date datetime)

    Insert into temporal Values('122321564','Type1','2015-11-09 14:00:02.000')

    Insert into temporal Values('545423130','Type1','2015-11-09 14:00:06.133')

    Insert into temporal Values('457898651','Type1','2015-11-09 14:00:08.433')

    Insert into temporal Values('455648751','Type1','2015-11-09 14:00:09.797')

    Insert into temporal Values('116203984','Type1','2015-11-09 14:00:14.517')

    Insert into temporal Values('878151201','Type1','2015-11-09 14:00:28.857')

    Insert into temporal Values('984106036','Type2','2015-11-09 14:00:31.240')

    Insert into temporal Values('237152812','Type2','2015-11-09 14:00:31.557')

    Insert into temporal Values('549122753','Type2','2015-11-09 14:00:32.833')

    Insert into temporal Values('963246913','Type2','2015-11-09 14:00:37.530')

    Insert into temporal Values('369155511','Type2','2015-11-09 14:00:37.560')

    Insert into temporal Values('852151705','Type2','2015-11-09 14:00:48.010')

    Insert into temporal Values('258155509','Type2','2015-11-09 14:00:48.777')

    Insert into temporal Values('528151156','Type2','2015-11-09 14:00:48.837')

    Insert into temporal Values('582100049','Type2','2015-11-09 14:00:54.967')

    Insert into temporal Values('741126218','Type2','2015-11-09 14:00:55.433')

    Insert into temporal Values('456106533','Type2','2015-11-09 14:00:56.153')

    Insert into temporal Values('654152815','Type2','2015-11-09 14:00:57.900')

    Insert into temporal Values('759123171','Type2','2015-11-09 14:00:58.007')

    Insert into temporal Values('987246914','Type2','2015-11-09 14:00:59.600')

    Insert into temporal Values('412110852','Type1','2015-11-09 14:00:59.647')

    Insert into temporal Values('874000763','Type1','2015-11-09 14:01:00.103')

    Insert into temporal Values('324151161','Type1','2015-11-09 14:01:07.187')

    Insert into temporal Values('753246910','Type1','2015-11-09 14:01:14.013')

    Insert into temporal Values('951152811','Type1','2015-11-09 14:01:29.477')

    Insert into temporal Values('154246908','Type1','2015-11-09 14:01:31.313')

    Insert into temporal Values('325151197','Type1','2015-11-09 14:01:31.923')

    Insert into temporal Values('457116206','Type1','2015-11-09 14:01:37.130')

    Insert into temporal Values('652100039','Type1','2015-11-09 14:01:41.017')

    Insert into temporal Values('459151198','Type1','2015-11-09 14:01:41.283')

    Insert into temporal Values('754246911','Type1','2015-11-09 14:01:46.710')

    Insert into temporal Values('745122754','Type1','2015-11-09 14:01:56.150')

    Insert into temporal Values('642152810','Type1','2015-11-09 14:01:57.707')

    Insert into temporal Values('754103673','Type3','2015-11-09 14:02:06.470')

    Insert into temporal Values('345155503','Type3','2015-11-09 14:02:07.110')

    Insert into temporal Values('666246903','Type3','2015-11-09 14:02:08.140')

    Insert into temporal Values('777152813','Type3','2015-11-09 14:02:13.447')

    Insert into temporal Values('759110854','Type3','2015-11-09 14:02:20.987')

    Insert into temporal Values('354123161','Type3','2015-11-09 14:02:27.583')

    Insert into temporal Values('749155513','Type3','2015-11-09 14:02:31.667')

    Insert into temporal Values('127106035','Type3','2015-11-09 14:02:35.863')

    Insert into temporal Values('124151189','Type3','2015-11-09 14:02:52.277')

    Insert into temporal Values('135151174','Type3','2015-11-09 14:02:59.680')

    Insert into temporal Values('128100053','Type2','2015-11-09 14:03:00.230')

    Insert into temporal Values('957103674','Type2','2015-11-09 14:03:01.210')

    Insert into temporal Values('324106531','Type2','2015-11-09 14:03:03.160')

    Insert into temporal Values('664152817','Type2','2015-11-09 14:03:11.150')

    Insert into temporal Values('559126219','Type2','2015-11-09 14:03:13.550')

    Insert into temporal Values('557116078','Type2','2015-11-09 14:03:13.987')

    Insert into temporal Values('556151173','Type2','2015-11-09 14:03:34.080')

    I’m looking to count (serials), group by type (consecutive records based on datetime) and take first and last record datetime.

    The output that I’m looking is

    QtyTypeDateFromDate To

    6Type12015-11-09 14:00:02.0002015-11-09 14:00:28.857

    14Type22015-11-09 14:00:31.2402015-11-09 14:00:59.600

    13Type12015-11-09 14:00:59.6472015-11-09 14:01:57.707

    10Type32015-11-09 14:02:06.4702015-11-09 14:02:59.680

    7Type22015-11-09 14:03:00.2302015-11-09 14:03:34.080

    Thanks in advance for your help.

  • November 16, 2015 at 8:39 am

    #1839980

    Thanks for posting up sample data - it makes this so much easier.

    Try this query for starters:

    SELECT

    Qty = COUNT(DISTINCT serial),

    [Type] = modelo,

    DateFrom = MIN([date]),

    DateTo = MAX([date])

    FROM (

    SELECT *,

    Grouper = ROW_NUMBER() OVER(ORDER BY [date]) -

    ROW_NUMBER() OVER(PARTITION BY modelo ORDER BY [date])

    FROM temporal

    ) d

    GROUP BY modelo, Grouper

    ORDER BY MIN([date])

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 16, 2015 at 8:48 am

    #1839986

    Thanks a lot, it works

  • November 16, 2015 at 9:33 am

    #1840009

    laorozco1 (11/16/2015)

    Thanks a lot, it works

    Since you're the one that will need to explain it and support it, do you know how and why it works?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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