在日常工作中，领导往往会检核企业的月度订单准时交付能力，确定影响订单准交的关键瓶颈，如何快速拉动数据，展现领导想要的真实数据很重要
1 declare @date1 varchar(6) 2 declare @date2 varchar(6) 3 set @date1 = LEFT(CONVERT(varchar(8),DateAdd(month,-36,getdate()),112),6) 4 set @date2 = LEFT(CONVERT(varchar(8),DateAdd(month,-1,getdate()),112),6) 5 SELECT DISTINCT LEFT(TD013,6) AS 年月,MB005 类别编码,MA003 类别名称 6 ,cast((cast(COUNTS1 as decimal(15,4))/Case When COUNTS2=0 then 1 else COUNTS2 end) as decimal(15,4)) 交付率 7 FROM COPTD 8 LEFT JOIN INVMB ON MB001=TD004 9 LEFT JOIN INVMA ON MB005=MA002 AND MA001='1' 10 LEFT JOIN 11 (Select MA002 类别1,LEFT(TD013,6) YM1,Count(Distinct TH014+TH015+TH016) COUNTS1 From COPTG --按照订单【预交货日】筛选同月已销且按时销货笔数 12 LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002 13 LEFT JOIN COPTD ON TD001=TH014 AND TD002=TH015 AND TD003=TH016 14 LEFT JOIN INVMB ON TD004=MB001 15 LEFT JOIN INVMA ON MA002= MB005 16 Where TG023='Y' AND TD013>=@date1 AND TD013<=@date2 AND MA001='1' and TG003<=TD013 17 GROUP BY LEFT(TD013,6),MA002 18 ) AS CH ON LEFT(TD013,6)=YM1 AND MB005=类别1 19 LEFT JOIN 20 (Select MA002 类别2, LEFT(TD013,6) YM2,Count(Distinct TD001+TD002+TD003) COUNTS2 From COPTD --按照订单【预交货日】筛选同月订单笔数 21 LEFT JOIN INVMB ON TD004=MB001 22 LEFT JOIN INVMA ON MA002= MB005 23 Where TD021='Y' AND TD013>=@date1 AND TD013<=@date2 AND MA001='1' 24 GROUP BY LEFT(TD013,6),MA002 25 ) AS YCH ON LEFT(TD013,6)=YM2 AND MB005=类别2 26 where TD013>=@date1 AND TD013<=@date2 27 ORDER BY MB005,年月