Need help of tricky Query

  • Hi Guys,

    Have got great help from this website over the years ,so first of all want to thank everyone here 🙂 and this is my first post as a member.

    I have a below data in a table

    ID Userid Startdate enddate

    1 6005 14-Apr-12 13:10 14-Apr-12 13:12

    1 6005 14-Apr-12 13:12 14-Apr-12 13:13

    1 6005 14-Apr-12 13:39 14-Apr-12 13:42

    1 6005 14-Apr-12 14:52 14-Apr-12 15:32

    1 6005 14-Apr-12 15:32 14-Apr-12 15:33

    What is required , I want the start date and the end date of row 1 should be <=60 mins of start date of row +1

    So the sample output of above would look like

    ID Userid Startdate enddate

    1 6005 14-Apr-12 13:10 14-Apr-12 13:42

    1 6005 14-Apr-12 14:52 14-Apr-12 15:32

    Basically divide each by a duration of 60 mins.. if there is any way of getting this without any cursor/loops?

    Any help on the above would be appreciated..thanks.

  • GonnaCatchIT (7/8/2012)


    Have got great help from this website over the years ...

    Welcome aboard!

    Shifting gears a bit, if what you say is true, then you should already know how to post readily consumable data to get tested answers by now. Help us help you in the future. Please see the first link in my signature line below for how to do that properly.

    I would also ask, what do you want returned for the following?

    ID Userid Startdate enddate

    1 6005 14-Apr-12 13:10 14-Apr-12 14:12 --62 minutes

    1 6005 14-Apr-12 14:12 14-Apr-12 14:13 --1 minute

    1 6005 14-Apr-12 14:13 14-Apr-12 15:15 --62 minutes

    1 6005 14-Apr-12 15:15 14-Apr-12 15:16 --1 minute

    1 6005 14-Apr-12 15:16 14-Apr-12 15:17 --1 minutes

    --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)

  • CREATE TABLE #CASE_DATA(CASE_ID INT,[USER_ID] INT,BEGIN_DATE DATETIME,END_DATE DATETIME)

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:12:00.000','2012-04-19 13:12:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:12:00.000','2012-04-19 13:12:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:39:00.000','2012-04-19 13:42:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:42:00.000','2012-04-19 13:42:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 14:51:00.000','2012-04-19 15:18:00.000')

    INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 15:18:00.000','2012-04-19 15:18:00.000')

    Expected output

    1295222 17164/19/2012 13:104/19/2012 13:42

    129522217164/19/2012 14:514/19/2012 15:18

    When somone is working on this case, it is not possible for another to do so at the same time, meaning we can assume that all consecutive rows with the same user id is the same action. The only exception would be if there is more than 60minutes between the End_date of Row X and Start_date of Row X+1 it will be another action.

  • -- comparing rows

    SELECT B.CASE_ID, B.[USER_ID] , E.END_DATE, B.BEGIN_DATE, datediff(MINUTE,E.END_DATE,B.BEGIN_DATE) as Diff

    INTO #WORK

    FROM (select ROW_NUMBER() OVER (ORDER BY [USER_ID]) AS 'RowNumber', *

    from CASE_DATA) B

    inner join (select (ROW_NUMBER() OVER (ORDER BY [USER_ID])+1) AS 'NextRowNumber', *

    from CASE_DATA) E ON B.RowNumber = E.NextRowNumber

    where datediff(MINUTE,E.END_DATE,B.BEGIN_DATE) >= 60

    -- grabbing records matching begin and end

    Select DISTINCT cd.*

    from CASE_DATA cd

    where cd.BEGIN_DATE in (SELECT BEGIN_DATE from #WORK) or cd.BEGIN_DATE in (SELECT END_DATE from #WORK)

    -- close to what you want

    didn't have much time but figured you might be able to cluge together this type of thing

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

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