Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
anchoredwisdom
Starting Member
22 Posts |
Posted - 2012-07-11 : 05:42:19
|
Table 1 CURR_STSLOB FP BRAND ITEM SUB_ITEM PART COST--------------------------------------------------------------LOB1 FP1 BRAND1 SKU1 10LOB1 FP1 BRAND1 SKU1 MOD1 20LOB1 FP1 BRAND1 SKU1 MOD1 PART1 30TABLE 2PRIOR_STSLOB FP BRAND ITEM SUB_ITEM PART COST-----------------------------------------------------------------LOB1 FP1 BRAND1 SKU1 MOD1 PART2 400LOB1 FP1 BRAND1 SKU1 MOD1 PART1 300LOB1 FP1 BRAND1 SKU1 MOD1 200LOB1 FP1 BRAND1 SKU1 100LOB1 FP1 BRAND1 SKU2 MOD2 PART2 10 Expected Output LOB ITEM SUB_ITEM PART CURRCOST PRIORCOST----------------------------------------------------------- LOB1 SKU1 10 100LOB1 SKU1 MOD1 20 200LOB1 SKU1 MOD1 PART1 30 300LOB1 SKU1 MOD1 PART2 400------------------------------------------------------------THe row which containts item=sku2 ( from table prior_sts) should not come as it is not present in cur_sts.I tried this query but it is not workingSELECT PR.LOB,PR.ITEM,PR.SUB_ITEM,PR.PART,PR.COST,CR.COSTFROM PRIOR_STS PR LEFT JOIN CURR_STS CRON PR.LOB=CR.LOB ANDPR.ITEM=CR.ITEM AND(CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) = (CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) AND(CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) = (CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) WHERE EXISTS( SELECT ‘X’ FROM CURR_STS C WHERE C.ITEM=PRIOR_STS.ITEM) |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2012-07-11 : 05:58:00
|
change "left join" to "inner join" and remove the where section |
|
|
anchoredwisdom
Starting Member
22 Posts |
Posted - 2012-07-11 : 08:33:02
|
Apologies for posting again.It happened as there was some error after i clicked on submit.I got the solution and i am posting the query below.SELECT PR.*,CR.COST AS CURRENT_COST FROM ( SELECT LOB,ITEM,(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,COSTFROM PRIOR_STS ) PR LEFT JOIN ( SELECT LOB,ITEM,(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,COSTFROM CURR_STS ) CRON PR.LOB=CR.LOB ANDPR.ITEM=CR.ITEM ANDPR.SUBITEM=CR.SUBITEM ANDPR.PART=CR.PARTWHERE EXISTS (SELECT 1 FROM CURR_STS C WHERE C.ITEM=PR.ITEM)ORDER BY COST |
|
|
|
|
|
|
|