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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Provided solution
question abandoned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.