locked
update line price if multiple invoice\item RRS feed

  • Question

  •   I have a Invoice table where I'm taking the value from a Price table and applying that at the Invoice\Item level. If
    there are multiple lines for same invoice\Item I need to take the Price and update line 1 only.

    Update pricebyline in the Invoice tbl where item(from Price tbl) matches. WHen this update runs with sample data I want 2.10 applied to invoice 39921 and line 1. The second, third,or fourth line(if Invoice had that many lines for same Item) will contain a zero. This way when you look at the data at Invoice level you get the price by item

    example:
    Price tbl:
    item z87a
    Price 2.10

    Invoice tbl
    Invoicenbr 39921
    line 1
    item z87a
    pricebyline 0

    Invoicenbr 39921
    line 2
    item z87a
    pricebyline 0

     
    Create Table #pricelist
    (item nvarchar(18), -pk
     applyprc dec(18,2));
     
     Create Table #invoices
    (inv nvarchar(12), -pk
     line int, -pk
     item nvarchar(18), -pk
     prcbyline dec(18,2));


     

    Thursday, May 25, 2017 1:33 AM

Answers

  • Create Table #pricelist
    (item nvarchar(18), --pk
     applyprc dec(18,2));
     
     Create Table #invoices
    (inv nvarchar(12), --pk
     line int, --pk
     item nvarchar(18), --pk
     prcbyline dec(18,2));
    
    Insert #pricelist(item, applyprc) Values('z87a', 2.10)
    Insert #invoices(inv, line, item, prcbyline) Values
    (39921, 1, 'z87a', 0),
    (39921, 2, 'z87a', 0);
    
    ;With cte As
    (Select i.item, i.prcbyline,
      Row_Number() Over(Partition By i.inv, i.item Order By i.line) As rn
    From #invoices i)
    Update c
    Set prcbyline = Case When c.rn = 1 Then p.applyprc Else 0 End
    From cte c
    Inner Join #pricelist p On c.item = p.item;
    
    --Check result
    Select * From #invoices;
    

    Tom
    • Proposed as answer by Xi Jin Thursday, May 25, 2017 5:09 AM
    • Marked as answer by hart60 Thursday, May 25, 2017 12:44 PM
    Thursday, May 25, 2017 2:26 AM

All replies

  • Create Table #pricelist
    (item nvarchar(18), --pk
     applyprc dec(18,2));
     
     Create Table #invoices
    (inv nvarchar(12), --pk
     line int, --pk
     item nvarchar(18), --pk
     prcbyline dec(18,2));
    
    Insert #pricelist(item, applyprc) Values('z87a', 2.10)
    Insert #invoices(inv, line, item, prcbyline) Values
    (39921, 1, 'z87a', 0),
    (39921, 2, 'z87a', 0);
    
    ;With cte As
    (Select i.item, i.prcbyline,
      Row_Number() Over(Partition By i.inv, i.item Order By i.line) As rn
    From #invoices i)
    Update c
    Set prcbyline = Case When c.rn = 1 Then p.applyprc Else 0 End
    From cte c
    Inner Join #pricelist p On c.item = p.item;
    
    --Check result
    Select * From #invoices;
    

    Tom
    • Proposed as answer by Xi Jin Thursday, May 25, 2017 5:09 AM
    • Marked as answer by hart60 Thursday, May 25, 2017 12:44 PM
    Thursday, May 25, 2017 2:26 AM
  •  Works great.

     Thanks!!!

    Thursday, May 25, 2017 12:44 PM
  •  Merge #invoices tgt
     using #pricelist src  on tgt.item=src.item
     WHEN Matched and tgt.line=1 then 
     Update 
     Set prcbyline=src.applyprc;
    

    Thursday, May 25, 2017 7:08 PM
  • Why did you fail to post correct DDL? Why did you not normalize your tables? Essentially you made your own problems with incorrect design.

     >> I have an invoice_nbrs [sic] table where I'm taking the value from a Price table and applying that at the invoice_nbr\Item level. If there are multiple lines for same invoice_nbr\Item I need to take the Price and update line 1 only. <<

    I hope not. What you are doing is copying a paper form into SQL table, and not doing a relational design at all. Oh, before I forget, the abbreviation “tbl” is called a Tibble and we make fun of people who do it. It mixes data and metadata in the same schema in violation of all the principles of good design.

    RDBMS is based on logical modeling. This means we don’t record the physical line numbers on a piece of paper or screen. This is a silly as identifying automobile by its parking space in one particular garage instead of using the VIN. 

    Have you ever seen an inventory kept in strings of Chinese characters of varying length? No of course not; so why did you allow it. Items are identified by some standard inventory number. If you’re lucky it’s an industry-standard. And if it’s an industry-standard the ISO convention (and every other source I know)will do it in a fixed length string of digits, Latin letters and perhaps some punctuation. Here is how your pricelist would have looked if you had written in SQL, instead of making up your own language. I like the global trade item numbers (GTIN), but you should use whatsoever standard in your industry. 

    CREATE TABLE Pricelist
    (gtin CHAR(15) NOT NULL PRIMARY KEY,
     item_price DECIMAL(18,2) NOT NULL
     CHECK (item_price >= 0.00));

    The Invoices table needs a reference back to the pricelist. This is why it’s called a referential database. You didn’t bother with that, so your data has no integrity. You also notice how DRI actions can handle changes to the data automatically without you having to write extra code.
     
    CREATE TABLE Invoices 
    (invoice_nbr CHAR(12) NOT NULL, 
     gtin CHAR(15) NOT NULL 
     REFERENCES Pricelist (gtin)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
     PRIMARY KEY (invoice_nbr, gtin),
     invoice_qty INTEGER NOT NULL CHECK(invoice_qty > 0)
    );

    This is a very common SQL programming idiom and you’ll see it over and over. Did you notice the price of an item is not an attribute of an invoice? It’s an attribute of a single item, and should not appear as part of the invoices table. This is a basic concept of normalization.


    >> Update pricebyline in the invoice_nbr tbl [sic: this table models invoices, not an identifier value] where item(from Price tbl[sic: a tibble and a singular table name) matches. When this update runs with sample data I want 2.10 applied to invoice_nbr 39921 and line 1. The second, third, or fourth line (if invoice_nbr had that many lines for same Item) will contain a zero. <<

    no! You’re making every line after the first one into a lie. It has the wrong information and you’re doing it deliberately! 

    Your obsessed with the physical storage of the data on paper, so you’re trying to write an old COBOL program where the formatting is done in a monolithic block of code. But SQL does not work that way. Think logical and not physical. 

    here’s another idiom you will see quite frequently. It’s a view that does the extension based on price list in the invoices involved. You do not have to keep updating things. The reason that occurred to you is exactly what you would have to do with paper forms and punch cards.

    CREATE VIEW Extended_Invoice
    AS
    SELECT I.gtin, P.item_price, I.invoice_qty, (I.invoice_qty * P.item_price)AS extension 
      FROM Pricelist AS P, Invoices AS I
     WHERE P.gtin = I.gtin

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 25, 2017 8:44 PM
  • I disagree with you here Celko - the price should be stored on the invoice. Prices change, invoices do not (hopefully).

    You want the stored in the invoice so you know the price at invoice time which may be different from the price today.

    Thursday, May 25, 2017 8:59 PM