SQL Server Performance

Get consecutive rows.

Discussion in 'SQL Server 2005 General Developer Questions' started by Access, Dec 12, 2006.

  1. Access New Member

    Hello all,

    I have a table Orders which has 3 columns : Invoice_Number, invoice_date and invoice_status.
    It looks like this:

    Invoice_Number | invoice_date | invoice_status
    ________________________________________________
    0001 | 01/12/2001 12:00 AM | Active
    0002 | 03/03/2001 2:22 PM | Closed
    0004 | 05/12/2001 11:00 AM | Closed
    0005 | 06/11/2001 10:00 AM | Active
    0006 | 11/12/2003 19:00 PM | Closed
    0009 | 12/12/2003 12:00 PM | Sent
    0010 | 01/27/2004 9:00 AM | Sent


    What I need to do is to get a consecutive invoice_number based on N parameter will be provided from UI.(user will enter how many consecutive invoices he want to see at a time, 3, 5 10 etc.)
    In this particular example if user enters 3, as an output he will get invoices 0004, 0005 and 0006.

    Thanks for your help.
  2. jezemine New Member

  3. FrankKalis Moderator

    I don't think this is about paging.

    Access, care to explain what you consider "consecutive"?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. Access New Member

    quote:Originally posted by FrankKalis

    I don't think this is about paging.

    Access, care to explain what you consider "consecutive"?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

    What I meant here is to get consecutive invoices. As an example lets say user enters number 5 on the UI, so I will need to get 5 consecutive invoice numbers:
    so I will start from invoice number = 0001 and try to count 5, but only got to 0003 so I will start counting again until I found 5 consecutive 0004, 0005,0006,0007,0008. Something like that.
  5. merrillaldrich New Member

    Sounds like
    TOP N ... order by invoice_number
    to me. Is that what you need?
  6. FrankKalis Moderator

    Actually I think it's quite more tricky than just a TOP n. Will this work?<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE tempDB<br />IF OBJECT_ID('access')&gt;0<br /> DROP TABLE access<br />CREATE TABLE access<br />(<br /> Invoice_Number INT<br /> , invoice_date SMALLDATETIME<br /> , invoice_status VARCHAR(10)<br />)<br />INSERT INTO access SELECT 1 , '01/12/2001 12:00 AM' , 'Active'<br />UNION ALL SELECT 2 , '03/03/2001 2:22 PM' , 'Closed'<br />UNION ALL SELECT 4 , '05/12/2001 11:00 AM' , 'Closed'<br />UNION ALL SELECT 5 , '06/11/2001 10:00 AM' , 'Active'<br />UNION ALL SELECT 6 , '11/12/2003 19:00 PM' , 'Closed'<br />UNION ALL SELECT 9 , '12/12/2003 12:00 PM' , 'Sent'<br />UNION ALL SELECT 10 , '01/27/2004 9:00 AM' , 'Sent'<br /><br />DECLARE @MyValue INT<br />SET @MyValue = 3<br /><br />SELECT t1.*<br /> FROM access t1<br /> JOIN <br /> (<br /> SELECT MAX(MyStart) MyStart , My_End <br /> FROM <br /> (SELECT t.Invoice_Number MyStart <br /> FROM access t <br /> WHERE NOT EXISTS<br /> (SELECT * <br /> FROM access <br /> WHERE Invoice_Number = t.Invoice_Number-1))starts <br /> JOIN <br /> (SELECT t.Invoice_Number My_End <br /> FROM access t <br /> WHERE NOT EXISTS<br /> (SELECT * <br /> FROM access <br /> WHERE Invoice_Number = t.Invoice_Number+1) )ends <br /> ON MyStart &lt;= My_End <br /> GROUP BY My_End <br /> HAVING My_End + 1 - MAX(MyStart) = @MyValue) x<br /> ON t1.Invoice_Number BETWEEN x.MyStart AND x.My_End<br /><br /> <br /><br />Invoice_Number invoice_date invoice_status <br />-------------- ------------------------------------------------------ -------------- <br />4 2001-05-12 11:00:00 Closed<br />5 2001-06-11 10:00:00 Active<br />6 2003-11-12 19:00:00 Closed<br /><br />(3 row(s) affected)<br /></font id="code"></pre id="code"><br />I guess it can be simplified, but honestly it's late in my place and I'm tired. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. Access New Member

    Thank for your reply Frank. The query works, but not exactly like I expected. It returns me all of the consecutive rows, but I need only first matched. I'll try to adjust it myself, but if you have time tomorrow please post your version.

    Thanks
  8. FrankKalis Moderator

    I suspected that this would cause a problem. For that case, you should modify the above query to:


    SELECT t1.*
    FROM access t1
    JOIN
    (
    SELECT TOP 1 MAX(MyStart) MyStart , My_End
    FROM
    (SELECT t.Invoice_Number MyStart
    FROM access t
    WHERE NOT EXISTS
    (SELECT *
    FROM access
    WHERE Invoice_Number = t.Invoice_Number-1))starts
    JOIN
    (SELECT t.Invoice_Number My_End
    FROM access t
    WHERE NOT EXISTS
    (SELECT *
    FROM access
    WHERE Invoice_Number = t.Invoice_Number+1) )ends
    ON MyStart <= My_End
    GROUP BY My_End
    HAVING My_End + 1 - MAX(MyStart) = @MyValue
    ORDER BY MyStart) x
    ON t1.Invoice_Number BETWEEN x.MyStart AND x.My_End

    However, reviewing the query at all, I think the following might be more effective. At least it seems to produce a leaner execution plan:


    SELECT t.*
    FROM access t
    JOIN
    (SELECT TOP 1 MIN(Invoice_Number) AS My_Start, MAX(Invoice_Number) AS My_End
    FROM
    (
    SELECT Invoice_Number, Invoice_Number -
    (SELECT COUNT(Invoice_Number) - MIN(Invoice_Number)
    FROM access t2
    WHERE t1.Invoice_Number >= t2.Invoice_Number) AS Run_Offset
    FROM access t1
    ) P
    GROUP BY Run_Offset
    HAVING COUNT(Invoice_Number) > @MyValue - 1
    ORDER BY My_Start) x
    ON t.Invoice_Number BETWEEN x.My_Start AND x.My_End



    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  9. FrankKalis Moderator

    Addition:
    In case you have the requirement that the first run having equal or more consecutive Invoice_Numbers than the number entered by the user, you should modify the HAVING clause to:


    HAVING COUNT(Invoice_Number) >= @MyValue

    And in case the consecutive Invoice_Numbers MUST exactly match the entered value (thus probably ignore lesser Invoice_Numbers with more consecutive numbers), change the HAVING clause to:


    HAVING COUNT(Invoice_Number) = @MyValue



    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  10. Access New Member

    quote:Originally posted by FrankKalis

    Addition:
    In case you have the requirement that the first run having equal or more consecutive Invoice_Numbers than the number entered by the user, you should modify the HAVING clause to:


    HAVING COUNT(Invoice_Number) >= @MyValue

    And in case the consecutive Invoice_Numbers MUST exactly match the entered value (thus probably ignore lesser Invoice_Numbers with more consecutive numbers), change the HAVING clause to:


    HAVING COUNT(Invoice_Number) = @MyValue



    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de


    It looks like it works, Thanks, but it takes to much time to get executed. The table that i run it against has 42000 records.
  11. FrankKalis Moderator

    Well, if the first performs better, you should take this. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Btw, when you're on SQL Server 2005, you might be able to take advantage of these new OLAP T-SQL functions. Unfortunately I can't get my hands on that version right now, so I can't help you here. <br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page