Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
mssql query about case when

Discussion mssql query about case when

  • Friday, January 25, 2013 9:28 AM
     
     
    Sign In to Vote
    0

    Hello,

    i have a query like this;

    SELECT        order_product.order_product_id, order_product.order_id, order_product.product_id, order_product.name, order_product.model, order_product.quantity, 
                             order_product.price, order_product.total, order_product.tax, product.tax_class_id AS TaxClass_ID, tax_rule.tax_rate_id, tax_rate.rate, case when tax_rate.rate = 10 then order_product.tax as tax1
    FROM            order_product, product, tax_rule, tax_rate
    WHERE        (product.tax_class_id = tax_rule.tax_class_id) AND (order_product.product_id = product.product_id) AND (tax_rule.tax_rate_id = tax_rate.tax_rate_id)
    GROUP BY order_product_id
    ORDER BY order_id DESC

    i have 2 different tax  rate in tax_rate.rate column , i want to check if tax rate is 10 than put the value of order_product.tax value   as  tax1 and and if tax_rate.rate is 2 than put the value of order_product.tax value   as  tax2 

    i have tried case when but i dont know how to make correct it.

    thank you

    b.regards

     

All Replies

  • Friday, January 25, 2013 9:47 AM
     
      Has Code
    Sign In to Vote
    1

    E.g. using INNER JOIN and table alias names for better readability:

    SELECT  OP.order_product_id ,
        OP.order_id ,
        OP.product_id ,
        OP.name ,
        OP.model ,
        OP.quantity ,
        OP.price ,
        OP.total ,
        OP.tax ,
        P.tax_class_id AS TaxClass_ID ,
        TRL.tax_rate_id ,
        TRT.rate ,
        CASE WHEN TRT.rate = 10 THEN OP.tax
             ELSE NULL
        END AS tax1 ,
        CASE WHEN TRT.rate = 2 THEN OP.tax
             ELSE NULL
        END AS tax2
FROM    order_product OP
        INNER JOIN product P ON OP.product_id = P.product_id
        INNER JOIN tax_rule TRL ON P.tax_class_id = TRL.tax_class_id
        INNER JOIN tax_rate TRT ON TRL.tax_rate_id = TRT.tax_rate_id
ORDER BY OP.order_id DESC;

    btw, change the type of this thread to question, please.

     
  • Friday, January 25, 2013 10:21 AM
     
     
    Sign In to Vote
    0

    Thank you

    B.Regards