declare @s varchar(4000) set @s='SELECT LREG,LDDREG, sum(CASE WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RF'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN BOX_20 ELSE 0 END) AS LIVE_20, sum(CASE WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RF'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN BOX_40 ELSE 0 END) AS LIVE_40, sum(CASE WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'')THEN BOX_40 ELSE 0 END) AS LIVE_RQ, sum(CASE WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'')THEN BOX_45 ELSE 0 END) AS LIVE_RQ45, sum(CASE WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE IN(''RQ'',''RF'') AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN TEU ELSE 0 END) AS LIVE_TEU, sum(CASE WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'')AND CONT_TYPE=''RF'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'' ) THEN BOX_20 ELSE 0 END) AS DRY_20, sum(CASE WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'')AND CONT_TYPE=''RF'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_40 ELSE 0 END) AS DRY_40, sum(CASE WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_40 ELSE 0 END) AS DRY_RQ, sum(CASE WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_45 ELSE 0 END) AS DRY_RQ45, sum(CASE WHEN CARGO_TYPE IN( ''GC'',''RD'',''DG'',''AW'') AND CONT_TYPE IN(''RQ'',''RF'') AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN TEU ELSE 0 END) AS DRY_TEU FROM '+ @TABLE +' WHERE SAIL_WEEK='''+@YEAR+RIGHT(@WEEK,2)+''' AND TRADELANE='''+@TRADELANE+''' GROUP BY LREG, LDDREG'
set @s='SELECT LREG,LDDREG,
sum(CASE
WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RF'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN BOX_20
ELSE 0
END) AS LIVE_20,
sum(CASE
WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RF'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN BOX_40
ELSE 0
END) AS LIVE_40,
sum(CASE
WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'')THEN BOX_40
ELSE 0
END) AS LIVE_RQ,
sum(CASE
WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'')THEN BOX_45
ELSE 0
END) AS LIVE_RQ45,
sum(CASE
WHEN CARGO_TYPE IN(''RF'',''DG'',''AW'',''RD'') AND CONT_TYPE IN(''RQ'',''RF'') AND (TEMP_CTL<>''0.00'' OR TEMP_CTL<>''0.00C'') THEN TEU
ELSE 0
END) AS LIVE_TEU,
sum(CASE
WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'')AND CONT_TYPE=''RF'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'' ) THEN BOX_20
ELSE 0
END) AS DRY_20,
sum(CASE
WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'')AND CONT_TYPE=''RF'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_40
ELSE 0
END) AS DRY_40,
sum(CASE
WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_40
ELSE 0
END) AS DRY_RQ,
sum(CASE
WHEN CARGO_TYPE IN( ''GC'',''RD'', ''DG'',''AW'') AND CONT_TYPE=''RQ'' AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN BOX_45
ELSE 0
END) AS DRY_RQ45,
sum(CASE
WHEN CARGO_TYPE IN( ''GC'',''RD'',''DG'',''AW'') AND CONT_TYPE IN(''RQ'',''RF'') AND (TEMP_CTL=''0.00'' OR TEMP_CTL=''0.00C'') THEN TEU
ELSE 0
END) AS DRY_TEU
FROM '+ @TABLE +'
WHERE
SAIL_WEEK='''+@YEAR+RIGHT(@WEEK,2)+'''
AND TRADELANE='''+@TRADELANE+'''
GROUP BY LREG, LDDREG'