insert into CTtemp.dbo.SEC_S_SCO_NEW(guid,pub_dt,creat_tm,updt_tm,rmrk,sec_cd,f0010,f0020,f0030,f0040,f0050,f0060,f0070,f0080,f0090,f0100,f0110,f0120,f0130,f0140,f0150,f0160,f0170,f0180,sum1,sum2,sum3,subsum,totalsum,lasttotal)
select NEWID() guid, a1.PUB_DT, GETDATE() creat_tm, NULL updt_tm, NULL rmrk, a1.SEC_CD,
b1.SCORE F0010, b1.SEGMENT F0020, b2.SCORE F0030, b2.SEGMENT F0040, b3.SCORE F0050, b3.SEGMENT F0060,
c1.SCORE F0070, c1.SEGMENT F0080, c2.SCORE F0090, c2.SEGMENT F0100, c3.SCORE F0110, c3.SEGMENT F0120,
d1.SCORE F0130, d1.SEGMENT F0140, d2.SCORE F0150, d2.SEGMENT F0160, d3.SCORE F0170, d3.SEGMENT F0180,
a2.SCORE Sum1, a3.SCORE Sum2, a4.SCORE Sum3, a5.SCORE SubSUM, a1.SCORE TotalSum,e.SCORE LastTotal
FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a1
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a2 ON a1.SEC_CD=a2.SEC_CD AND a1.pub_dt=a2.pub_dt AND a2.SCO_ID='0101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a3 ON a1.SEC_CD=a3.SEC_CD AND a1.pub_dt=a3.pub_dt AND a3.SCO_ID='0102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a4 ON a1.SEC_CD=a4.SEC_CD AND a1.pub_dt=a4.pub_dt AND a4.SCO_ID='0103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a5 ON a1.SEC_CD=a5.SEC_CD AND a1.pub_dt=a5.pub_dt AND a5.SCO_ID='0104'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b1 ON a1.SEC_CD=b1.SEC_CD AND a1.pub_dt=b1.pub_dt AND b1.SCO_ID='010101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b2 ON a1.SEC_CD=b2.SEC_CD AND a1.pub_dt=b2.pub_dt AND b2.SCO_ID='010102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b3 ON a1.SEC_CD=b3.SEC_CD AND a1.pub_dt=b3.pub_dt AND b3.SCO_ID='010103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c1 ON a1.SEC_CD=c1.SEC_CD AND a1.pub_dt=c1.pub_dt AND c1.SCO_ID='010201'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c2 ON a1.SEC_CD=c2.SEC_CD AND a1.pub_dt=c2.pub_dt AND c2.SCO_ID='010202'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c3 ON a1.SEC_CD=c3.SEC_CD AND a1.pub_dt=c3.pub_dt AND c3.SCO_ID='010203'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d1 ON a1.SEC_CD=d1.SEC_CD AND a1.pub_dt=d1.pub_dt AND d1.SCO_ID='010301'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d2 ON a1.SEC_CD=d2.SEC_CD AND a1.pub_dt=d2.pub_dt AND d2.SCO_ID='010302'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d3 ON a1.SEC_CD=d3.SEC_CD AND a1.pub_dt=d3.pub_dt AND d3.SCO_ID='010303'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT e ON a1.SEC_CD=e.SEC_CD AND e.pub_dt=(SELECT min(PUB_DT) FROM (select top 2 pub_dt from CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT where sco_id='01' order by pub_dt desc) a) AND e.SCO_ID=a1.SCO_ID
WHERE a1.SCO_ID='01'
AND a1.PUB_DT = (SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT)
----------------
出錯:
消息 8180，級別 16，狀態(tài) 1，第 1 行
未能準備語句。
消息 207，級別 16，狀態(tài) 3，第 1 行
列名 'Col2201' 無效。
消息 207，級別 16，狀態(tài) 3，第 1 行
列名 'Col2204' 無效。
把最后left join 改為inner join 就可以了。
--------------------------------------------------------------------------------------------------------------------------
insert into CTtemp.dbo.SEC_S_SCO_NEW(guid,pub_dt,creat_tm,updt_tm,rmrk,sec_cd,f0010,f0020,f0030,f0040,f0050,f0060,f0070,f0080,f0090,f0100,f0110,f0120,f0130,f0140,f0150,f0160,f0170,f0180,sum1,sum2,sum3,subsum,totalsum,lasttotal)
select NEWID() guid, a1.PUB_DT, GETDATE() creat_tm, NULL updt_tm, NULL rmrk, a1.SEC_CD,
b1.SCORE F0010, b1.SEGMENT F0020, b2.SCORE F0030, b2.SEGMENT F0040, b3.SCORE F0050, b3.SEGMENT F0060,
c1.SCORE F0070, c1.SEGMENT F0080, c2.SCORE F0090, c2.SEGMENT F0100, c3.SCORE F0110, c3.SEGMENT F0120,
d1.SCORE F0130, d1.SEGMENT F0140, d2.SCORE F0150, d2.SEGMENT F0160, d3.SCORE F0170, d3.SEGMENT F0180,
a2.SCORE Sum1, a3.SCORE Sum2, a4.SCORE Sum3, a5.SCORE SubSUM, a1.SCORE TotalSum,e.SCORE LastTotal
FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a1
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a2 ON a1.SEC_CD=a2.SEC_CD AND a1.pub_dt=a2.pub_dt AND a2.SCO_ID='0101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a3 ON a1.SEC_CD=a3.SEC_CD AND a1.pub_dt=a3.pub_dt AND a3.SCO_ID='0102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a4 ON a1.SEC_CD=a4.SEC_CD AND a1.pub_dt=a4.pub_dt AND a4.SCO_ID='0103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a5 ON a1.SEC_CD=a5.SEC_CD AND a1.pub_dt=a5.pub_dt AND a5.SCO_ID='0104'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b1 ON a1.SEC_CD=b1.SEC_CD AND a1.pub_dt=b1.pub_dt AND b1.SCO_ID='010101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b2 ON a1.SEC_CD=b2.SEC_CD AND a1.pub_dt=b2.pub_dt AND b2.SCO_ID='010102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b3 ON a1.SEC_CD=b3.SEC_CD AND a1.pub_dt=b3.pub_dt AND b3.SCO_ID='010103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c1 ON a1.SEC_CD=c1.SEC_CD AND a1.pub_dt=c1.pub_dt AND c1.SCO_ID='010201'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c2 ON a1.SEC_CD=c2.SEC_CD AND a1.pub_dt=c2.pub_dt AND c2.SCO_ID='010202'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c3 ON a1.SEC_CD=c3.SEC_CD AND a1.pub_dt=c3.pub_dt AND c3.SCO_ID='010203'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d1 ON a1.SEC_CD=d1.SEC_CD AND a1.pub_dt=d1.pub_dt AND d1.SCO_ID='010301'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d2 ON a1.SEC_CD=d2.SEC_CD AND a1.pub_dt=d2.pub_dt AND d2.SCO_ID='010302'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d3 ON a1.SEC_CD=d3.SEC_CD AND a1.pub_dt=d3.pub_dt AND d3.SCO_ID='010303'
INNER JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT e ON a1.SEC_CD=e.SEC_CD AND e.pub_dt=(SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT where sco_id=a1.SCO_ID and pub_dt<a1.pub_dt) AND e.SCO_ID=a1.SCO_ID WHERE a1.SCO_ID='01'
AND a1.PUB_DT = (SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT)