locked
Filter the equalent field RRS feed

  • Question



  • SET NOCOUNT ON DECLARE @Product TABLE (Product_ID VarChar(50), Product_Name VarChar(50), Company_ID_1 VarChar(50) , Company_ID_2 VarChar(50), Company_ID_3 VarChar(50));
    INSERT INTO @Product Values ('1','Product_Name_1','P1_1','P2_1','P3_1');
    INSERT INTO @Product Values ('2','Product_Name_2','P1_2','P2_2','P3_2');
    INSERT INTO @Product Values ('3','Product_Name_3','P1_3','P2_3','P3_3');
    INSERT INTO @Product Values ('4','Product_Name_4','P1_4','P2_4','P3_4');
    INSERT INTO @Product Values ('5','Product_Name_5','P1_5','P3_5','P3_5');

    select P.* from (
    select T.*, coalesce(MIN(case when R.Price > 0 then R.Price end),0) as Avg_Price from (

    Select Product_ID, Product_Name, Company_ID_1, Company_ID_2, Company_ID_3,
    CASE Company_ID_1 WHEN 'P1_1' THEN 10 WHEN 'P1_2' THEN 12 WHEN 'P1_3' THEN 13 WHEN 'P1_4' THEN 13 WHEN 'P1_5' THEN 13  Else 0 END AS [Price_1] ,
    CASE Company_ID_1 WHEN 'P1_1' THEN 5 WHEN 'P1_2' THEN 7 WHEN 'P1_3' THEN 2 WHEN 'P1_4' THEN 1 WHEN 'P1_5' THEN 1  Else 0 END AS [Quantity_1] ,
    CASE Company_ID_2 WHEN 'P2_1' THEN 90 WHEN 'P2_2' THEN 22 WHEN 'P2_3' THEN 34 WHEN 'P2_4' THEN 6 WHEN 'P2_5' THEN 12  Else 0 END AS [Price_2] ,
    CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 2 WHEN 'P2_3' THEN 3 WHEN 'P2_4' THEN 5 WHEN 'P2_5' THEN 1  Else 0 END AS [Quantity_2] ,
    CASE Company_ID_3 WHEN 'P3_1' THEN 10 WHEN 'P3_3' THEN 20 Else 0 END AS [Price_3] ,
    CASE Company_ID_3 WHEN 'P3_1' THEN 2 WHEN 'P3_3' THEN 1 Else 0 END AS [Quantity_3]  
    from @Product

    ) as T cross apply (select Price_1 union all select Price_2 union all select Price_3) as R(Price)
    group by Product_ID, Product_Name,  Company_ID_1, Company_ID_2, Company_ID_3, Price_1, Price_2, Price_3 , Quantity_1, Quantity_2, Quantity_3)
    as P

     

    Above query lines are working fine.

    I need to take out the "Quantity" of the product as per the "Avg_Price". how?

    Saturday, December 10, 2011 11:56 AM

Answers

  • Check the below query updated to calculate Remark column value.

    SET NOCOUNT ON DECLARE @Product TABLE (Product_ID VarChar(50), Product_Name VarChar(50), Company_ID_1 VarChar(50) , Company_ID_2 VarChar(50), Company_ID_3 VarChar(50));
     INSERT INTO @Product Values ('1','Product_Name_1','P1_1','P2_1','P3_1');
     INSERT INTO @Product Values ('2','Product_Name_2','P1_2','P2_2','P3_2');
     INSERT INTO @Product Values ('3','Product_Name_3','P1_3','P2_3','P3_3');
     INSERT INTO @Product Values ('4','Product_Name_4','P1_4','P2_4','P3_4');
     INSERT INTO @Product Values ('5','Product_Name_5','P1_5','P3_5','P3_5');
     
    select P.*, X.Qty AS Quantity, X.Remark from (
     select T.*, coalesce(MIN(case when R.Price > 0 then R.Price end),0) as Avg_Price from (
     
    Select Product_ID, Product_Name, Company_ID_1, Company_ID_2, Company_ID_3,
     CASE Company_ID_1 WHEN 'P1_1' THEN 10 WHEN 'P1_2' THEN 12 WHEN 'P1_3' THEN 13 WHEN 'P1_4' THEN 13 WHEN 'P1_5' THEN 13  Else 0 END AS [Price_1] ,
     CASE Company_ID_1 WHEN 'P1_1' THEN 5 WHEN 'P1_2' THEN 7 WHEN 'P1_3' THEN 2 WHEN 'P1_4' THEN 1 WHEN 'P1_5' THEN 1  Else 0 END AS [Quantity_1] ,
     CASE Company_ID_1 WHEN 'P1_1' THEN 1 WHEN 'P1_3' THEN 1 Else 0 END AS [Remark_1] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 90 WHEN 'P2_2' THEN 22 WHEN 'P2_3' THEN 34 WHEN 'P2_4' THEN 6 WHEN 'P2_5' THEN 12  Else 0 END AS [Price_2] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 2 WHEN 'P2_3' THEN 3 WHEN 'P2_4' THEN 5 WHEN 'P2_5' THEN 1  Else 0 END AS [Quantity_2] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 1  WHEN 'P2_3' THEN 1 Else 0 END AS [Remark_2] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 10 WHEN 'P3_3' THEN 20 Else 0 END AS [Price_3] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 2 WHEN 'P3_3' THEN 1 Else 0 END AS [Quantity_3] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 1 Else 0 END AS [Remark_3]
     from @Product
     
    ) as T cross apply (select Price_1 union all select Price_2 union all select Price_3) as R(Price)
     group by Product_ID, Product_Name,  Company_ID_1, Company_ID_2, Company_ID_3, Price_1, Price_2, Price_3 , Quantity_1, Quantity_2, Quantity_3, Remark_1, Remark_2, Remark_3)
     as P 
     
     
    cross apply (
                     SELECT    MAX(Qty) Qty, MAX(Remark) Remark
                     FROM    (
                                 SELECT CASE 
                                    WHEN P.Avg_Price = P.Price_1 THEN P.Quantity_1 ELSE 0 END AS Qty,
                                    CASE WHEN P.Remark_1 = 1 THEN 1 ELSE 0 END AS Remark
                                 UNION ALL
                                 SELECT CASE
                                     WHEN P.Avg_Price = P.Price_2 THEN P.Quantity_2 ELSE 0 END,
                                     CASE WHEN P.Remark_2 = 1 THEN 1 ELSE 0 END
                                 UNION ALL
                                 SELECT CASE
                                     WHEN P.Avg_Price = P.Price_3 THEN P.Quantity_3 ELSE 0 END,
                                     CASE WHEN P.Remark_3 = 1 THEN 1 ELSE 0 END
                             ) AS T
                 ) X
    

    Thanks!

    • Proposed as answer by Naomi N Sunday, December 11, 2011 5:27 AM
    • Marked as answer by Alex Q8 Sunday, December 11, 2011 10:28 AM
    Saturday, December 10, 2011 10:19 PM

All replies

  • The above query i used to taking minimum Price of the product .  Same time i need to take out how many "Quantity" of product is available in the min price


    Below is the above query output

    Product_ID -----------------------------------------------------------------------------Avg_Price
    1    Product_Name_1    P1_1    P2_1    P3_1    10    5    90    1    10    2    10
    2    Product_Name_2    P1_2    P2_2    P3_2    12    7    22    2    0      0    12
    3    Product_Name_3    P1_3    P2_3    P3_3    13    2    34    3    20    1    13
    4    Product_Name_4    P1_4    P2_4    P3_4    13    1    6      5    0      0    6
    5    Product_Name_5    P1_5    P3_5    P3_5    13    1    0      0    0      0    13

    I need out put like below


    Product_ID -----------------------------------------------------------------------------Avg_Price  Quantity

    1    Product_Name_1    P1_1    P2_1    P3_1    10    5    90    1    10    2    10             5
    2    Product_Name_2    P1_2    P2_2    P3_2    12    7    22    2    0      0    12             7
    3    Product_Name_3    P1_3    P2_3    P3_3    13    2    34    3    20    1    13             2
    4    Product_Name_4    P1_4    P2_4    P3_4    13    1    6      5    0      0    6               5
    5    Product_Name_5    P1_5    P3_5    P3_5    13    1    0      0    0      0    13             1

    • Edited by Alex Q8 Saturday, December 10, 2011 1:10 PM
    Saturday, December 10, 2011 1:06 PM
  • Check the below query.

    It is written assuming that you need the maximum of quantity when there exists multiple quantities for the same avg_price. As in row 1, you have 2 quantities 5 and 2 for the same average price 10 and in this case, maximum quantity (which is 5) is being retrieved.

    select P.*, X.Qty AS Quantity
    from (
     select T.*, coalesce(MIN(case when R.Price > 0 then R.Price end),0) as Avg_Price from (
     
    Select Product_ID, Product_Name, Company_ID_1, Company_ID_2, Company_ID_3,
     CASE Company_ID_1 WHEN 'P1_1' THEN 10 WHEN 'P1_2' THEN 12 WHEN 'P1_3' THEN 13 WHEN 'P1_4' THEN 13 WHEN 'P1_5' THEN 13  Else 0 END AS [Price_1] ,
     CASE Company_ID_1 WHEN 'P1_1' THEN 5 WHEN 'P1_2' THEN 7 WHEN 'P1_3' THEN 2 WHEN 'P1_4' THEN 1 WHEN 'P1_5' THEN 1  Else 0 END AS [Quantity_1] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 90 WHEN 'P2_2' THEN 22 WHEN 'P2_3' THEN 34 WHEN 'P2_4' THEN 6 WHEN 'P2_5' THEN 12  Else 0 END AS [Price_2] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 2 WHEN 'P2_3' THEN 3 WHEN 'P2_4' THEN 5 WHEN 'P2_5' THEN 1  Else 0 END AS [Quantity_2] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 10 WHEN 'P3_3' THEN 20 Else 0 END AS [Price_3] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 2 WHEN 'P3_3' THEN 1 Else 0 END AS [Quantity_3]  
     from @Product 
    
    ) as T cross apply (select Price_1 union all select Price_2 union all select Price_3) as R(Price)
     group by Product_ID, Product_Name,  Company_ID_1, Company_ID_2, Company_ID_3, Price_1, Price_2, Price_3 , Quantity_1, Quantity_2, Quantity_3)
     as P
     cross apply (
    				SELECT	MAX(Qty) Qty
    				FROM	(
    							SELECT CASE 
    								WHEN P.Avg_Price = P.Price_1 THEN P.Quantity_1 ELSE 0 END AS Qty
    							UNION ALL
    							SELECT CASE
    								WHEN P.Avg_Price = P.Price_2 THEN P.Quantity_2 ELSE 0 END
    							UNION ALL
    							SELECT CASE
    								WHEN P.Avg_Price = P.Price_3 THEN P.Quantity_3 ELSE 0
    							END
    						) AS T
    			) X
    

    Thanks!

    • Proposed as answer by Naomi N Sunday, December 11, 2011 5:26 AM
    Saturday, December 10, 2011 3:12 PM
  • Thanks, its working fine...

    I need to know one thing.

    I update my query with remarks

    I have one condition only based on remark. if any companies provide "remark", (It will show as "1" else "0") . it is not based on "Quantity" and "Price"

    SET NOCOUNT ON DECLARE @Product TABLE (Product_ID VarChar(50), Product_Name VarChar(50), Company_ID_1 VarChar(50) , Company_ID_2 VarChar(50), Company_ID_3 VarChar(50));
    INSERT INTO @Product Values ('1','Product_Name_1','P1_1','P2_1','P3_1');
    INSERT INTO @Product Values ('2','Product_Name_2','P1_2','P2_2','P3_2');
    INSERT INTO @Product Values ('3','Product_Name_3','P1_3','P2_3','P3_3');
    INSERT INTO @Product Values ('4','Product_Name_4','P1_4','P2_4','P3_4');
    INSERT INTO @Product Values ('5','Product_Name_5','P1_5','P3_5','P3_5');

    select P.*, X.Qty AS Quantity from (
    select T.*, coalesce(MIN(case when R.Price > 0 then R.Price end),0) as Avg_Price from (

    Select Product_ID, Product_Name, Company_ID_1, Company_ID_2, Company_ID_3,
    CASE Company_ID_1 WHEN 'P1_1' THEN 10 WHEN 'P1_2' THEN 12 WHEN 'P1_3' THEN 13 WHEN 'P1_4' THEN 13 WHEN 'P1_5' THEN 13  Else 0 END AS [Price_1] ,
    CASE Company_ID_1 WHEN 'P1_1' THEN 5 WHEN 'P1_2' THEN 7 WHEN 'P1_3' THEN 2 WHEN 'P1_4' THEN 1 WHEN 'P1_5' THEN 1  Else 0 END AS [Quantity_1] ,
    CASE Company_ID_1 WHEN 'P1_1' THEN 1 WHEN 'P1_3' THEN 1 Else 0 END AS [Remark_1] ,
    CASE Company_ID_2 WHEN 'P2_1' THEN 90 WHEN 'P2_2' THEN 22 WHEN 'P2_3' THEN 34 WHEN 'P2_4' THEN 6 WHEN 'P2_5' THEN 12  Else 0 END AS [Price_2] ,
    CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 2 WHEN 'P2_3' THEN 3 WHEN 'P2_4' THEN 5 WHEN 'P2_5' THEN 1  Else 0 END AS [Quantity_2] ,
    CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 1  WHEN 'P2_3' THEN 1 Else 0 END AS [Remark_2] ,
    CASE Company_ID_3 WHEN 'P3_1' THEN 10 WHEN 'P3_3' THEN 20 Else 0 END AS [Price_3] ,
    CASE Company_ID_3 WHEN 'P3_1' THEN 2 WHEN 'P3_3' THEN 1 Else 0 END AS [Quantity_3] ,
    CASE Company_ID_3 WHEN 'P3_1' THEN 1 Else 0 END AS [Remark_3]
    from @Product

    ) as T cross apply (select Price_1 union all select Price_2 union all select Price_3) as R(Price)
    group by Product_ID, Product_Name,  Company_ID_1, Company_ID_2, Company_ID_3, Price_1, Price_2, Price_3 , Quantity_1, Quantity_2, Quantity_3, Remark_1, Remark_2, Remark_3)
    as P

    cross apply (
                    SELECT    MAX(Qty) Qty
                    FROM    (
                                SELECT CASE
                                    WHEN P.Avg_Price = P.Price_1 THEN P.Quantity_1 ELSE 0 END AS Qty
                                UNION ALL
                                SELECT CASE
                                    WHEN P.Avg_Price = P.Price_2 THEN P.Quantity_2 ELSE 0 END
                                UNION ALL
                                SELECT CASE
                                    WHEN P.Avg_Price = P.Price_3 THEN P.Quantity_3 ELSE 0
                                END
                            ) AS T
                ) X

    need Out put like below

    --- Avg_Price    Quantity    Remark

    --- 10                5               1
    --- 12                7               1
    --- 13                2               1
    --- 6                  5               0
    --- 13                1               0

     

    IF any company provide "Remarks" to there product, show the output result(Remark) as 1 else 0


    • Edited by Alex Q8 Saturday, December 10, 2011 8:45 PM
    Saturday, December 10, 2011 8:45 PM
  • Check the below query updated to calculate Remark column value.

    SET NOCOUNT ON DECLARE @Product TABLE (Product_ID VarChar(50), Product_Name VarChar(50), Company_ID_1 VarChar(50) , Company_ID_2 VarChar(50), Company_ID_3 VarChar(50));
     INSERT INTO @Product Values ('1','Product_Name_1','P1_1','P2_1','P3_1');
     INSERT INTO @Product Values ('2','Product_Name_2','P1_2','P2_2','P3_2');
     INSERT INTO @Product Values ('3','Product_Name_3','P1_3','P2_3','P3_3');
     INSERT INTO @Product Values ('4','Product_Name_4','P1_4','P2_4','P3_4');
     INSERT INTO @Product Values ('5','Product_Name_5','P1_5','P3_5','P3_5');
     
    select P.*, X.Qty AS Quantity, X.Remark from (
     select T.*, coalesce(MIN(case when R.Price > 0 then R.Price end),0) as Avg_Price from (
     
    Select Product_ID, Product_Name, Company_ID_1, Company_ID_2, Company_ID_3,
     CASE Company_ID_1 WHEN 'P1_1' THEN 10 WHEN 'P1_2' THEN 12 WHEN 'P1_3' THEN 13 WHEN 'P1_4' THEN 13 WHEN 'P1_5' THEN 13  Else 0 END AS [Price_1] ,
     CASE Company_ID_1 WHEN 'P1_1' THEN 5 WHEN 'P1_2' THEN 7 WHEN 'P1_3' THEN 2 WHEN 'P1_4' THEN 1 WHEN 'P1_5' THEN 1  Else 0 END AS [Quantity_1] ,
     CASE Company_ID_1 WHEN 'P1_1' THEN 1 WHEN 'P1_3' THEN 1 Else 0 END AS [Remark_1] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 90 WHEN 'P2_2' THEN 22 WHEN 'P2_3' THEN 34 WHEN 'P2_4' THEN 6 WHEN 'P2_5' THEN 12  Else 0 END AS [Price_2] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 2 WHEN 'P2_3' THEN 3 WHEN 'P2_4' THEN 5 WHEN 'P2_5' THEN 1  Else 0 END AS [Quantity_2] ,
     CASE Company_ID_2 WHEN 'P2_1' THEN 1 WHEN 'P2_2' THEN 1  WHEN 'P2_3' THEN 1 Else 0 END AS [Remark_2] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 10 WHEN 'P3_3' THEN 20 Else 0 END AS [Price_3] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 2 WHEN 'P3_3' THEN 1 Else 0 END AS [Quantity_3] ,
     CASE Company_ID_3 WHEN 'P3_1' THEN 1 Else 0 END AS [Remark_3]
     from @Product
     
    ) as T cross apply (select Price_1 union all select Price_2 union all select Price_3) as R(Price)
     group by Product_ID, Product_Name,  Company_ID_1, Company_ID_2, Company_ID_3, Price_1, Price_2, Price_3 , Quantity_1, Quantity_2, Quantity_3, Remark_1, Remark_2, Remark_3)
     as P 
     
     
    cross apply (
                     SELECT    MAX(Qty) Qty, MAX(Remark) Remark
                     FROM    (
                                 SELECT CASE 
                                    WHEN P.Avg_Price = P.Price_1 THEN P.Quantity_1 ELSE 0 END AS Qty,
                                    CASE WHEN P.Remark_1 = 1 THEN 1 ELSE 0 END AS Remark
                                 UNION ALL
                                 SELECT CASE
                                     WHEN P.Avg_Price = P.Price_2 THEN P.Quantity_2 ELSE 0 END,
                                     CASE WHEN P.Remark_2 = 1 THEN 1 ELSE 0 END
                                 UNION ALL
                                 SELECT CASE
                                     WHEN P.Avg_Price = P.Price_3 THEN P.Quantity_3 ELSE 0 END,
                                     CASE WHEN P.Remark_3 = 1 THEN 1 ELSE 0 END
                             ) AS T
                 ) X
    

    Thanks!

    • Proposed as answer by Naomi N Sunday, December 11, 2011 5:27 AM
    • Marked as answer by Alex Q8 Sunday, December 11, 2011 10:28 AM
    Saturday, December 10, 2011 10:19 PM