sqlsql-serversubquery

SQL new column value based from another column in the same table


I have a table with invoice sales lines and I am trying to make a query to generate a new column with the value from another column transaction in the same table.

I tried with CASE and subqueries, but the columns fills the value when meets the criteria:

enter image description here

select [No_], 
    [Document No_], 
    Quantity, 
    [Line Discount _], 
    [Unit Price], 
    [new_column] = (
        select si.Quantity
        where CONVERT(decimal,si.[Unit Price]) = 0 )
from [Sales Invoice Lines] si

I need the new_column values in the transaction where the Unit Price is != 0.

I am trying to get the Quantity values for the transactions where the Unit Price column has a value and the No_ is the same:

enter image description here


Solution

  • The best solution is to go back in time and populate this column when generating the free sales line(s). But now that that horse have left the barn, you can fetch them in a correlated subquery:

    select ProductNO, Doc, Qty, Discount, Price
    , CASE WHEN price > 0 THEN (select SUM(qty) FROM invoice_line il WHERE il.Doc= i.Doc AND il.Price = 0 AND il.ProductNO= i.ProductNO) END AS new_column
    FROM invoice_line i
    ...
    
    

    I'm guessing that production number of the free line should match the product number of the paid line, since you didn't bother mention it. This will also not work if multiple of the same paid products exist