Get consecutive rows 1

[lllyxer]

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.

 

[bborissov]

OK,, user add 3 and how do you know which invoices you want?
What happens if the user add 72?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[lllyxer]

User do not ADD , he will enter 3 as a parameter to pass to the query. If user enters 3 I basically want to query the table and find first 3 consecutive matches. I he enters 72, the query should look for 72 consecutive rows.

 

[bborissov]

OK! User don't add.
Here we go (I hope there is a more effective way):
Make a SP and pass wanted consecutive rows as parameter, Assume that @nWantedRows is the name of that parameter

SET DATEFORMAT MDY

DECLARE @MyTable TABLE (Invoice_Number Char(6), Invoice_Date datetime, Invoice_Status varchar(50))
INSERT INTO @MyTable VALUES ('0001','01/12/2001 12:00 AM','Active')
INSERT INTO @MyTable VALUES ('0002','03/03/2001 2:22 PM','Closed')
INSERT INTO @MyTable VALUES ('0004','05/12/2001 11:00 AM','Closed')
INSERT INTO @MyTable VALUES ('0005','06/11/2001 10:00 AM','Active')
INSERT INTO @MyTable VALUES ('0006','11/12/2003 19:00 PM','Closed')
INSERT INTO @MyTable VALUES ('0009','12/12/2003 12:00 PM','Sent')
INSERT INTO @MyTable VALUES ('0010','01/27/2004 9:00 AM','Sent')


--- Just for testing
DECLARE @nWantedRows int
SET @nWantedRows = 3


DECLARE @Test TABLE (Invoice_Number Char(6),
                     invoice_date   DateTime,
                     invoice_status varchar(50),
                     IWantThisRow bit)
DECLARE @nCalkRows int
SET @nCalkRows = 0
DECLARE @nMinInvoiceNumber int
DECLARE @nOldMinInvoiceNumber int
SELECT @nMinInvoiceNumber = MIN(CAST(Invoice_Number as int)) 
       FROM @MyTable
SET @nOldMinInvoiceNumber = @nMinInvoiceNumber-1
WHILE @nCalkRows < @nWantedRows
      BEGIN

          print @nMinInvoiceNumber
          print @nOldMinInvoiceNumber

          IF @nMinInvoiceNumber <> @nOldMinInvoiceNumber+1
             BEGIN
                 UPDATE @Test SET IWantThisRow = 0 WHERE IWantThisRow = 1
                 SET @nCalkRows = 0
             END
           INSERT INTO @Test 
           SELECT Invoice_Number,
                  invoice_date,
                  invoice_status,
                  1 
                  FROM @MyTable
                  WHERE CAST(Invoice_Number as int) =
                        @nMinInvoiceNumber
          SET @nCalkRows = @nCalkRows + 1
 
          SET @nOldMinInvoiceNumber  = @nMinInvoiceNumber
          SELECT @nMinInvoiceNumber =
                 MIN(CAST(Invoice_Number as int)) 
          FROM @MyTable
          WHERE CAST(Invoice_Number as int) > 
                @nMinInvoiceNumber

          IF @nMinInvoiceNumber IS NULL
             BEGIN 
                IF @nCalkRows < @nWantedRows
                   UPDATE @Test SET IWantThisRow = 0 WHERE IWantThisRow = 1
                BREAK
             END
         
      END

SELECT * from @Test WHERE IWantThisRow = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jbenson001]

In your example, why invoices 4,5 and 6? If he enters 3 would you not start at 1? What is the criteria for a "consecutive" invoice?

 

[bborissov]

If I remember well lllyxer wants just the first 3 (or what digit user enter) number of invoices starting from the beginning. In example data that He/She (Sorry lllyxer I don't know your gender only from your nick) provided the first 3 consecutive invoices are 4,5,6.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLBill]

According to what the poster said, it appears they want the 'first x consecutive rows'. With x being the input number.

so:

1
2
4
5
9
10
11
12

If I entered 2, the result should return only 1 & 2 (first 2 consecutive rows. Even though 4&5 meet the criteria, they aren't the FIRST occurance.). If I enter 4, I should get 9, 10, 11, 12 as that's the first 4 consecutive rows.

Illyxer, let us know if my assumption is correct or not.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Illyxer,

One question just came up. What if your user enters 2 and the data is:

1
2
3
4
6
7
9

Do you want 1 & 2 returned? It is the first occurance of two consecutive rows, it's also the first of 4. Or do you want 6 & 7 returned as it is ONLY 2 consecutive rows?

-SQLBill

Posting advice: FAQ481-4875

 

[lllyxer]

****************************
If I entered 2, the result should return only 1 & 2 (first 2 consecutive rows. Even though 4&5 meet the criteria, they aren't the FIRST occurance.). If I enter 4, I should get 9, 10, 11, 12 as that's the first 4 consecutive rows.

Illyxer, let us know if my assumption is correct or not.

*****************************

You are absolutely correct here.

 

[lllyxer]

*********************************************************
Illyxer,

One question just came up. What if your user enters 2 and the data is:

1
2
3
4
6
7
9

Do you want 1 & 2 returned? It is the first occurance of two consecutive rows, it's also the first of 4. Or do you want 6 & 7 returned as it is ONLY 2 consecutive rows?
*******************************************************

The outpt her will be the first 4.

Thanks a lot guys.

 

[SQLBill]

So the number they request is the MINIMUM? You want what matches and any additional following consecutive rows?

-SQLBill

Posting advice: FAQ481-4875

 

[lllyxer]

**********************************
So the number they request is the MINIMUM? You want what matches and any additional following consecutive rows?
**********************************

Yes, you are correct.