July 25, 2014 at 2:23 pm
Hi,
I got stuck at one scenario. The requirement is, I have CompanyCode, ItemNumber and PlannerNumbers in a table and the values are shown below,
CompanyCodeItemCode PlannerCode
10 15066-00266-01 10053357
96 15066-00266-01 10048778
10 19200-00027-06 10052915
96 19200-00027-06 0
I should i always display companycode 96 planners information. If there is no plannercode for an item then check if there is any plannercode for company 10 for the same item, if planner exists then display company 10 planner to company 96.
In the above example 19200-00027-06 item does not contain plannercode for company 96, then we display its companycode 10 planner for this item.. finally want the result set like
CompanyCodeItemCode PlannerCode
96 15066-00266-01 10048778
96 19200-00027-06 10052915
Could some one tell me how can write an sql statement for this requirement?
Any help would be greatly appreciated.
Thanks,
Venu.
July 25, 2014 at 2:31 pm
Thanks for the sample data. Next time (if you would please) try to provide it in a way similar to what I've done. This will, in more complex questions, save a ton of time. Plus some people won't even answer questions without it. 🙂
At any rate, here's a solution for you.
DECLARE @test-2 TABLE
(CompanyCode INT
,ItemCode VARCHAR(20)
,PlannerCode BIGINT)
INSERT @test-2
VALUES
(10, '15066-00266-01', 10053357)
,(96, '15066-00266-01', 10048778)
,(10, '19200-00027-06', 10052915)
,(96, '19200-00027-06', 0)
SELECT
t1.CompanyCode
,t1.ItemCode
,t1.PlannerCode
,COALESCE(NULLIF(t1.PlannerCode,0),t2.PlannerCode) AS derived_PlannerCode
FROM
@test-2 AS t1
LEFT JOIN @test-2 AS t2
ON t1.ItemCode = t2.ItemCode
AND t2.CompanyCode <> 96
WHERE
t1.CompanyCode = 96
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 2, 2014 at 12:34 am
Agree with Jason. just one suggestion
instead of "AND t2.CompanyCode <> 96" , It will be good if you use "AND t2.CompanyCode = 10" , if you have more data for one Item code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply