report to check the result for 10 consucutive minutes

  • Frank Kalis gave me some code off-line that might help.  I am rather thick-skulled, so he had to give two versions - the use of modulo made more sense to me. 

    If this helps, thank Frank: 

     

    --Farrell, you can either use '19000101' or 0.

    CREATE TABLE #time( [ID] INT IDENTITY,

                                       dt DATETIME,

                                       ReasonID INT)

    INSERT INTO #time

    SELECT '20050427 10:50:00', NULL -- 1

    UNION ALL

    SELECT '20050427 10:51:00', NULL -- 2

    UNION ALL

    SELECT '20050427 10:52:00', NULL -- 3

    UNION ALL

    SELECT '20050427 10:53:00', 20206 -- 4

    UNION ALL

    SELECT '20050427 10:54:00', 20206 -- 5

    UNION ALL

    SELECT '20050427 10:55:00', NULL -- 6

    UNION ALL

    SELECT '20050427 10:56:00', 20212 -- 7

          

    SELECT CONVERT( varchar(1), [ID]) AS [ID],

                 DATEADD( minute, -DATEPART( minute, dt) % 5, dt) AS DateByFiveSeconds,

                 -DATEPART( minute, dt) % 5 AS Modulo,

                 dt 

    FROM #time

    DROP TABLE #time

    -- Both will produce the same result. The former is interpreted by SQL Server as a date by converting the string to a DATETIME.

    -- The latter is converted from an INT to DATETIME. In both cases it's the server's base date.

    -- Basically it's the same trick you can use to set the time in a DATETIME column to midnight.

    -- This technique works with all the allowed parameters for DATEADD and DATEDIFF.

    -- Here it strips off the seconds and milliseconds.

    /*

    SELECT DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0),

     COUNT(*) - SUM( CASE WHEN ReasonID IS NULL THEN 0 ELSE 1 END) AS Running,

     COUNT(*) - SUM( CASE WHEN ReasonID IS NOT NULL THEN 0 ELSE 1 END) AS Down

    FROM #time

    GROUP BY DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0)

    */

    I wasn't born stupid - I had to study.

  • Is this solution working for you or I still need to knock it out?

  • I am still working with the above hint.Not successful so far.

  • Id

    ----

    7513

    7514

    7515

    7516

    7517

    7518

    7519

    7520

    7521

    7522

    7523

    7524

    7525

    7526

    7527

    7528

    7529

    7530

    7531

    7532

    7533

    7534

    7535

    7536

    7537

    7538

    7539

    ----

    7568

    7569

    7570

    7571

    7572

    7573

    7574

    7575

    7576

    7577

    7578

    7579

    7580

    7581

    7582

    7583

    7584

    7585

    7586

    7587

    7588

    7589

    7590

    7591

    7592

    7593

    7594

    7595

    7596

    ----

    7688

    ----

    7695

    7696

    7697

    7698

    -----

    7700

    -----

    IF I have these Ids with me and the data between the two dashed lines is a series of contiguius data.

    how can I get  the following result. Starting and ending ids of the series.

    7539-7513

    7596-7568

    7688-7688

    7698-7695

    7700-7700

    Thanks.

  • -- create table ids( n int)

    -- insert into ids (n) values (7535)

    -- insert into ids (n) values (7536)

    -- insert into ids (n) values (7537)

    -- insert into ids (n) values (7538)

    -- insert into ids (n) values (7539)

    -- insert into ids (n) values (7568)

    -- insert into ids (n) values (7569)

    -- insert into ids (n) values (7570)

    -- insert into ids (n) values (7571)

    -- insert into ids (n) values (7572)

    -- insert into ids (n) values (7573)

    -- insert into ids (n) values (7574)

    -- insert into ids (n) values (7575)

    -- insert into ids (n) values (7576)

    -- insert into ids (n) values (7577)

    -- insert into ids (n) values (7578)

    -- insert into ids (n) values (7579)

    -- insert into ids (n) values (7580)

    -- insert into ids (n) values (7581)

    -- insert into ids (n) values (7582)

    -- insert into ids (n) values (7583)

    -- insert into ids (n) values (7584)

    -- insert into ids (n) values (7585)

    -- insert into ids (n) values (7586)

    -- insert into ids (n) values (7587)

    -- insert into ids (n) values (7588)

    -- insert into ids (n) values (7589)

    -- insert into ids (n) values (7590)

    -- insert into ids (n) values (7591)

    -- insert into ids (n) values (7592)

    -- insert into ids (n) values (7593)

    -- insert into ids (n) values (7594)

    -- insert into ids (n) values (7595)

    -- insert into ids (n) values (7596)

    -- insert into ids (n) values (7688)

    -- insert into ids (n) values (7695)

    -- insert into ids (n) values (7696)

    -- insert into ids (n) values (7697)

    -- insert into ids (n) values (7698)

    -- insert into ids (n) values (7700)

    select start

     ,(select min(i3.n)

           from ids i3

                  where i3.n >= n1.start 

                  and not exists (select* from ids i4 where i4.n = i3.n +1 )) as [end]

    from

    (

    select n as start

    from ids i1

    where 

    not exists (select *

                from ids i2

         where i2.n = i1.n -1)

    ) n1

    hth

     


    * Noel

  • Looking at the required result

    number name result start_date

    89277 xxxx no 8/11/05 5:12 AM

    89287 xxxx no 8/11/05 5:22 AM

    86405 xxxx yes 8/9/05 5:01 AM

    90000 xxxx no 9/9/04 5:15 AM

    Should the last record be there?  The date range for that set of 10 is 9/9/04 to  9/15/04...  I do see that the times are consecutive though.

  • num  name   

    7534  tt

    7535  tt

    7536  tt

    7537  nn

    7538  nn

    7539  tt

     

     

    From the above data set how can get the following result

    start  end name

    7534 7536   tt

    7537 7538   nn

    7539 7539   tt

     

    How can I get  the above resukt.

     

    Thanks.

  • sahana smthing similar :

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=191316#bm191447


    Kindest Regards,

    Vasc

  • I'm still looking at it and I still don't get it... can you share the idea behind this technic?

  • I m using pair of consecutive rows from a self join

    row1 will pair with row2

    and I extract the rows where is a variance in group value

    for example from 50 to 100 or 50 to null

    and by picking the 2 rows that do the variance I obtain in 1 case the min from a group and afther that the max for a group (in the two derived tables)

    after this I join the tables and I eliminate the unwanted rows MIN> MAX and by selecting the pairs with the same MAX that has the MAX(MIN) : )) otherwise it means that the pair is not good

    : )

     

     


    Kindest Regards,

    Vasc

  • Looks like I didn't go to school long enough .

    Thanx for the tip.

  • Still I could not make the solution with ur tip. Can anyone give the straight solution to my problem.

     

    Thanks.

  • Sahana can you post your table + sample data (declare @t table....

    insert  so I just copy in queryAnalixzer : )

    and the result that you want


    Kindest Regards,

    Vasc

  • create table #ids( n int,name varchar(20))

     insert into #ids (n,name) values (7534,'tt')

     insert into #ids (n,name) values (7535,'tt')

     insert into #ids (n,name) values (7536,'tt')

     insert into #ids (n,name) values (7537,'nn')

     insert into #ids (n,name) values (7538,'nn')

     insert into #ids (n,name) values (7539,'tt')

    Start end  COunt name

    7534  7536  3      tt

    7537  7538  2      nn

    7539  7539  1      tt

  • create table #ids( n int,name varchar(20))

     insert into #ids (n,name) values (7534,'tt')

     insert into #ids (n,name) values (7535,'tt')

     insert into #ids (n,name) values (7536,'tt')

     insert into #ids (n,name) values (7537,'nn')

     insert into #ids (n,name) values (7538,'nn')

     insert into #ids (n,name) values (7539,'tt')

    /*

    Start end  COunt name

    7534  7536  3      tt

    7537  7538  2      nn

    7539  7539  1      tt

    */

    SELECT X.name,MAX(Y.n),X.n,X.n-MAX(Y.n)+1 as Count

    FROM

     (SELECT a.n,a.name

      FROM #ids a LEFT OUTER JOIN #ids b

      ON a.name=b.name AND a.n=b.n-1

      WHERE b.name IS NULL

      ) X

    INNER JOIN

     (SELECT a.n,a.name

      FROM #ids a LEFT OUTER JOIN #ids b

      ON a.name=b.name AND a.n=b.n+1

      WHERE b.name IS NULL

      ) Y

    ON X.name=Y.name

    WHERE Y.n<=X.n

    GROUP BY X.name,X.n

    DROP TABLE #ids


    Kindest Regards,

    Vasc

Viewing 15 posts - 16 through 29 (of 29 total)

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