SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display One CompanyCode values instead of other companycode


Display One CompanyCode values instead of other companycode

Author
Message
yvbchowdary
yvbchowdary
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 132
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,
CompanyCode ItemCode 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

CompanyCode ItemCode 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.
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6906 Visits: 4115
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 TABLE
(CompanyCode INT
,ItemCode VARCHAR(20)
,PlannerCode BIGINT)

INSERT @test
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 AS t1
LEFT JOIN @test 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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
rahul.rahuzz
rahul.rahuzz
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 169
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search