Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display One CompanyCode values instead of other companycode Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:12 AM
Points: 7, Visits: 89
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.
Post #1596405
Posted Friday, July 25, 2014 2:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Post #1596407
Posted Tuesday, September 2, 2014 12:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:16 PM
Points: 18, Visits: 166
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.
Post #1609444
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse