SQL Server Performance Forum – Threads Archive
Get consecutive rows.
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.
sounds like you want a way to do paging? http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx www.elsasoft.org
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
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
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.
Sounds like
TOP N … order by invoice_number
to me. Is that what you need?
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’)>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 <= 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>
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
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
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
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.
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
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>
]]>