| Author |
Topic |
|
goligol
Posting Yak Master
128 Posts |
 Posted - 2013-10-14 : 13:39:00
|
| Hi,I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b. Here is table a:CREATE TABLE #A( naics INT, ust_code INT, port INT, all_qty_1_yr FLOAT, all_qty_2_yr FLOAT, all_val_yr FLOAT, all_air_val_yr FLOAT, all_air_wgt_yr FLOAT, all_ves_val_yr FLOAT, all_ves_wgt_yr FLOAT, all_cnt_val_yr FLOAT, all_cnt_wgt_yr FLOAT, all_border_val_yr FLOAT);INSERT INTO #A values ('11111000','2010','2002','8070569.14298579','0','2335641254.30021','0','0','2335641254.30021','8156408492.66667','0','0','0');INSERT INTO #A values ('11111000','2230','2010','280841.478063446','0','84622385.9133129','0','0','84622385.9133129','299600780.773355','0','0','0');INSERT INTO #A values ('11111000','2410','1401','25735','0','12305667','0','0','12305667','25719794','0','0','0');INSERT INTO #A values ('11111000','3330','1401','173','0','60820','0','0','60820','173020','35258','61020','0');INSERT INTO #A values ('11111000','3570','1303','16','0','6090','0','0','6090','16738','6090','16738','0');INSERT INTO #A values ('11111000','4840','2002','192000','0','54232320','0','0','54232320','192000000','0','0','0');INSERT INTO #A values ('11111000','5170','2002','38860','0','15063318','0','0','15063318','38860260','0','0','0');INSERT INTO #A values ('11111000','5490','1401','32845','0','9673200','0','0','9673200','32669142','0','0','0');INSERT INTO #A values ('11111000','5490','3604','226','0','75000','0','0','0','0','0','0','75000');INSERT INTO #A values ('11111000','5590','1401','633','0','306808','0','0','306808','635310','306808','635310','0');and here is table b:CREATE TABLE #B( naics INT, ust_code INT, port INT, stat_month INT, Cum_qty_1_mo FLOAT, Cum_qty_2_mo FLOAT, Cum_all_val_mo FLOAT, Cum_air_val_mo FLOAT, Cum_air_wgt_mo FLOAT, Cum_ves_val_mo FLOAT, );INSERT INTO #B values ('11111000','1220','0106','01','2','0','3440','0','0','0');INSERT INTO #B values ('11111000','1220','0107','03','14','0','3442','0','0','0');INSERT INTO #B values ('11111000','1220','0108','09','0','0','0','0','0','0');INSERT INTO #B values ('11111000','1220','0109','08','0','0','0','0','0','0');INSERT INTO #B values ('11111000','1220','0127','05','14','0','3499','0','0','0');INSERT INTO #B values ('11111000','1220','0212','12','323','0','107137','0','0','0');INSERT INTO #B values ('11111000','1220','0701','01','2','0','3513','0','0','0');INSERT INTO #B values ('11111000','1220','0708','08','24','0','7814','0','0','0');INSERT INTO #B values ('11111000','1220','0712','08','24','0','7814','0','0','0');INSERT INTO #B values ('11111000','1220','0715','12','207','0','60110','0','0','0');I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’ /****** Script for SelectTopNRows command from SSMS ******/SELECT a.[naics],a.[ust_code],a.[port],a.[all_qty_1_yr],a.[all_qty_2_yr],a.[all_val_yr],a.[all_air_val_yr],a.[all_air_wgt_yr],a.[all_ves_val_yr],a.[all_ves_wgt_yr],a.[all_cnt_val_yr],a.[all_cnt_wgt_yr],a.[all_border_val_yr],b.[stat_month],b.[Cum_qty_1_mo] [Cum_qty_1_mo]_Jan --(Feb, ..., DEC),b.[Cum_qty_2_mo] [Cum_qty_2_mo]_Jan --(Feb, ...,DEC),b.[Cum_all_val_mo] [Cum_all_val_mo]_Jan --(Feb, ...,DEC),b.[Cum_air_val_mo] [Cum_air_val_mo]_Jan --(Feb, ...,DEC)FROM [trade].[dev].[8DigitsNaicsValueWeightShare_PORTS_exp] aleft join ( SELECT [naics],[ust_code],[port],[stat_month],[Cum_qty_1_mo],[Cum_qty_2_mo],[Cum_all_val_mo],[Cum_air_val_mo],[Cum_air_wgt_mo],[Cum_ves_val_mo]FROM [trade].[dev].[8DigitsNaicsCumMonth_PORTS_exp]) b ona.[naics] = b.naicsand a.ust_code = b.ust_codeand a.port = b.portwhere b.stat_month = '01' --Janleft join where b.stat_month = '02' – Feb***** output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like * columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)Thank you |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-10-14 : 15:33:42
|
Perhaps something like:select a.naics ,a.ust_code ,a.port ,a.all_qty_1_yr ,a.all_qty_2_yr ,a.all_val_yr ,a.all_air_val_yr ,a.all_air_wgt_yr ,a.all_ves_val_yr ,a.all_ves_wgt_yr ,a.all_cnt_val_yr ,a.all_cnt_wgt_yr ,a.all_border_val_yr ,sum(case when b.stat_month='01' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Jan ,sum(case when b.stat_month='01' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Jan ,sum(case when b.stat_month='01' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Jan ,sum(case when b.stat_month='01' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Jan ,sum(case when b.stat_month='02' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Feb ,sum(case when b.stat_month='02' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Feb ,sum(case when b.stat_month='02' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Feb ,sum(case when b.stat_month='02' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Feb ,sum(case when b.stat_month='03' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Mar ,sum(case when b.stat_month='03' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Mar ,sum(case when b.stat_month='03' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Mar ,sum(case when b.stat_month='03' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Mar ,sum(case when b.stat_month='04' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Apr ,sum(case when b.stat_month='04' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Apr ,sum(case when b.stat_month='04' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Apr ,sum(case when b.stat_month='04' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Apr ,sum(case when b.stat_month='05' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_May ,sum(case when b.stat_month='05' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_May ,sum(case when b.stat_month='05' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_May ,sum(case when b.stat_month='05' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_May ,sum(case when b.stat_month='06' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Jun ,sum(case when b.stat_month='06' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Jun ,sum(case when b.stat_month='06' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Jun ,sum(case when b.stat_month='06' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Jun ,sum(case when b.stat_month='07' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Jul ,sum(case when b.stat_month='07' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Jul ,sum(case when b.stat_month='07' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Jul ,sum(case when b.stat_month='07' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Jul ,sum(case when b.stat_month='08' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Aug ,sum(case when b.stat_month='08' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Aug ,sum(case when b.stat_month='08' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Aug ,sum(case when b.stat_month='08' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Aug ,sum(case when b.stat_month='09' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Sep ,sum(case when b.stat_month='09' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Sep ,sum(case when b.stat_month='09' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Sep ,sum(case when b.stat_month='09' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Sep ,sum(case when b.stat_month='10' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Oct ,sum(case when b.stat_month='10' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Oct ,sum(case when b.stat_month='10' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Oct ,sum(case when b.stat_month='10' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Oct ,sum(case when b.stat_month='11' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Nov ,sum(case when b.stat_month='11' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Nov ,sum(case when b.stat_month='11' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Nov ,sum(case when b.stat_month='11' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Nov ,sum(case when b.stat_month='12' then b.Cum_qty_1_mo else 0 end) as Cum_qty_1_mo_Dec ,sum(case when b.stat_month='12' then b.Cum_qty_2_mo else 0 end) as Cum_qty_2_mo_Dec ,sum(case when b.stat_month='12' then b.Cum_all_val_mo else 0 end) as Cum_all_val_mo_Dec ,sum(case when b.stat_month='12' then b.Cum_air_val_mo else 0 end) as Cum_air_val_mo_Dec from trade.dev.8DigitsNaicsValueWeightShare_PORTS_exp as a left outer join trade.dev.8DigitsNaicsCumMonth_PORTS_exp as b on b.naics=a.naics and b.ust_code=a.ust_code and b.port=a.port group by a.naics ,a.ust_code ,a.port ,a.all_qty_1_yr ,a.all_qty_2_yr ,a.all_val_yr ,a.all_air_val_yr ,a.all_air_wgt_yr ,a.all_ves_val_yr ,a.all_ves_wgt_yr ,a.all_cnt_val_yr ,a.all_cnt_wgt_yr ,a.all_border_val_yr By the way, the sample data you provided gives no month data from second table as ust_code from first table doesn't match second table. |
 |
|
|
|
|
|
Joining tables through multiple criteria