I have following table.I want Query in SQL as per output Table

Table.pngI have add one column like 'AdditionQty' and value of that column like which orderid is '0' for particular Adviceid as well as Itemid. Second one is if Orderid  0 is found but that itemid  is not match  in that Adviceid then min adviceid add that Qty as AdditionQty.I want output like below table...

OutPut Table
Jaymin SathavaraAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try full and tested solution for you -

Data Generation

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

Open in new window



Solution

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

Open in new window


/*------------------------
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)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Provided solution
question abandoned.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.