Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: increment by one while ?

    Hello,

    I am trying to figure out how to assign a value of 1 to a record and it will increment by one while the customer number is still the same. Once the customer number changes it will assign that new record for that customer a one and increment by one and so on.

    Below is a table that shows the data structure. The filed Count Inv by Cust is the output i am trying to get.

    customer invoice amount Count_Inv_By_Cust
    1 111 10.00 1
    1 112 150.00 2
    1 113 145.00 3
    2 114 110.00 1
    2 115 75.00 2



    All help is appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what database are you using? this isn't an ANSI SQL question

    if it's msaccess, see this thread

    if it's mysql, that feature is built in to the auto_increment facility

    if it's some other database, you may want to investigate doing it in your application logic, e.g. with a transaction lock on retrieving the last number used

    but the real question here is: why do your numbers have to have that format?

    what's wrong with invoice numbers that are not magically tied to the customer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Well the reason why i am trying to do this is because i am trying to come up with a method where i can compare every customers first invoice to their second invoice.

    If there is an easier wahy to do this the idea is more than welcome. This was just one of the first solutions i thought of.

    Thanks Again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    every customer's first invoice is the invoice for that customer with the lowest date

    every customer's second invoice is the invoice for that customer with the lowest date that isn't the lowest date

    of course, this means you need to add a date to your invoice table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    Okay. The table does have a datetime field for the invoice date.

    So i guess i would select the min(invoice_date) and min(invoice_numb) for the customers first invoice.

    So to get the second invoice I would do the same but i can't be the same invoice as the one selected first. Let me see what i can come up with to get the second part.

    Thanks Again.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the customer's first invoice --
    Code:
    select ...
      from invoices as t
     where invoice_date =
           ( select max(invoice_date)
               from invoices
              where customer = t.customer )
    the second invoice involves another nesting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I ran into a similar problem... in general, given a table Invoices like yours:

    SELECT P.*, (SELECT COUNT(*) FROM Invoices Q WHERE P.customer = Q.customer AND P.invoice_date> Q.invoice_date) AS InvoiceOrder FROM Invoices P

    Err... maybe that should be a less than sign. Anyway, save it as a view and you can find the Nth invoice per customer quite easily.

  8. #8
    Join Date
    Jan 2004
    Posts
    164

    Increment by 1 while Customer ID is the Same

    Hello,

    I am trying to figure out how to assign a value of 1 to a record and it will increment by one while the customer id is still the same. Once the customer id changes it will assign that new record for that customer a 1 and increment by one and so on.

    Below is a table that shows the data structure. The filed Count Inv by Cust is the output i am trying to get.

    customer invoice amount Count_Inv_By_Cust
    1 111 10.00 1
    1 112 150.00 2
    1 113 145.00 3
    2 114 110.00 1
    2 115 75.00 2



    All help is appreciated.

  9. #9
    Join Date
    Jan 2008
    Location
    Billings, MT
    Posts
    14
    You can use ROW_NUMBER() to do this.

    SELECT Customer,
    Invoice,
    Amount,
    ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Invoice) AS RecID
    FROM TableName
    Tom Rupsis
    Granite Peak Systems
    Phone: 406-672-8292
    Email: trupsis@granitepeaksys.com
    LinkedIn: www.linkedin.com/in/trupsis

  10. #10
    Join Date
    Jan 2004
    Posts
    164
    Perfect! That worked exactly the way i needed it to.

    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •