Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

AdviceId DetailId OrderId ItemId Qty
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

Expected 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

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

Advertisements