SQL Puzzle | The Additional Quantity Puzzle

In this puzzle you have to generate a new column called AdditionQty. When the value of a orderid is 0 then the first row of that advice should have the value of Qty other wise just show NULL. Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Advices
(
	 AdviceId INT
	,DetailId INT 
	,OrderId INT 
	,ItemId INT 
	,Qty INT
)

INSERT INTO Advices VALUES
(19140,76841,73036,6194,400),
(19140,76842,73037,437,500),
(19140,76843,73038,6194,550),
(19140,76844,0,6194,100),
(19141,76845,10001,6194,100),
(19141,76846,10002,6100,50),
(19141,76847,10004,6194,50),
(19142,76848,10006,6100,50),
(19142,76849,10006,6100,50),
(19142,76850,0,437,10),
(19143,76851,10101,250,100),
(19143,76851,10102,251,20),
(19143,76853,10103,252,10),
(19143,76853,0,253,5)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT AdviceId  ,  DetailId  ,  OrderId ,    ItemId    ,  r.Qty
	, CASE WHEN rnk = 1 THEN rq.Qty ELSE NULL END AdditionQty 
	FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY AdviceId ORDER BY DetailId) rnk
	FROM Advices
)r
OUTER APPLY 
(
	SELECT TOP 1 QTY FROM Advices a
	WHERE a.AdviceId = r.AdviceId 
	AND a.OrderId = 0
)rq


--

Output

--

/*------------------------
OUTPUT 
------------------------*/
AdviceId    DetailId    OrderId     ItemId      Qty         AdditionQty
----------- ----------- ----------- ----------- ----------- -----------
19140       76841       73036       6194        400         100
19140       76842       73037       437         500         NULL
19140       76843       73038       6194        550         NULL
19140       76844       0           6194        100         NULL
19141       76845       10001       6194        100         NULL
19141       76846       10002       6100        50          NULL
19141       76847       10004       6194        50          NULL
19142       76848       10006       6100        50          10
19142       76849       10006       6100        50          NULL
19142       76850       0           437         10          NULL
19143       76851       10101       250         100         5
19143       76851       10102       251         20          NULL
19143       76853       10103       252         10          NULL
19143       76853       0           253         5           NULL

(14 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com