关于期初期末值累加的SQL语句

 

--select * from ReportDataSource order by CreationDate desc

declare @StartDate varchar(10)='';
with temp1 as  ---temp1用于获取每天的提单数，过滤条件已写入
(
 select CONVERT(varchar(100),submitDate,23) as StaticDay,COUNT(*) as Submit                                                                       
 from T_Defect_All vd where 1=1  and SubmitDate<='2014-3-6 23:59:59'  and vd.PLIPMT='eb1b4686-b608-41c8-b8d5-524d79495b16'
 and vd.PRODUCT='PP000599' and vd.RELEASE='e0315c3c-49d5-4842-abfb-c2ee5ea0ea13' group by CONVERT(varchar(100),submitDate,23)                                                                    
),                     
temp2 as    ---temp2用于获取每天的修改数，过滤条件已写入                      
(                         
   select CONVERT(varchar(100),DefectModifiedTime,23) as StaticDay,COUNT(*) as Midify                                                                    
   from T_Defect_All vd where 1=1  and DefectModifiedTime<='2014-3-6 23:59:59' 
   and vd.PLIPMT='eb1b4686-b608-41c8-b8d5-524d79495b16' and vd.PRODUCT='PP000599' and vd.RELEASE='e0315c3c-49d5-4842-abfb-c2ee5ea0ea13'
   group by CONVERT(varchar(100),DefectModifiedTime,23)                       
 ),                     
 temp3 as  --temp3用于获取每天的关闭单，过滤条件已写入                   
 (                         
   select CONVERT(varchar(100),ConfirmTime,23) as StaticDay,COUNT(*) as Confirm                                                                      
   from T_Defect_All vd where 1=1  and ConfirmTime<='2014-3-6 23:59:59' and Status='0'  and vd.PLIPMT='eb1b4686-b608-41c8-b8d5-524d79495b16'
   and vd.PRODUCT='PP000599' and vd.RELEASE='e0315c3c-49d5-4842-abfb-c2ee5ea0ea13'                         
   group by CONVERT(varchar(100),ConfirmTime,23)                     
  ),                     
 temp4 as --temp4采用Full join 的方式 将提单数，修改数，关闭数按天汇总                    
 (                             
   select isnull(temp1.StaticDay,isnull(temp2.StaticDay,temp3.StaticDay)) as SubmitDay,isnull(temp1.Submit,0) as Submit,
   isnull(temp2.Midify,0) as Midify,isnull(temp3.Confirm,0) as Confirm from temp1 full join temp2 on temp1.StaticDay =temp2.StaticDay                                             
   full join temp3 on temp2.StaticDay =temp3.StaticDay                     
 ),                     
 temp5 as  --temp5是在temp4的基础上按天累加，进行循环相加                   
 (                         
  select SubmitDay,DATENAME(Year,SubmitDay)+'第'+DATENAME(week,SubmitDay)+'周' as SubmitWeek,dbo.F_GetWeekRegion(SubmitDay) as WeekName,                         
  SumSubmit=ISNULL((select SUM(Submit)from temp4 where SubmitDay<=a.SubmitDay),(select Sum(Submit) from temp4 where SubmitDay<=case @StartDate when '' then (select Min(SubmitDay) from temp4) else @StartDate end)),
  SumMidify=ISNULL((select SUM(Midify)from temp4 where SubmitDay<=a.SubmitDay),(select Sum(Midify) from temp4 where SubmitDay<=case @StartDate when '' then (select Min(SubmitDay) from temp4) else @StartDate end)),                         
  SumConfirm=ISNULL((select SUM(Confirm)from temp4 where SubmitDay<=a.SubmitDay),(select Sum(Confirm) from temp4 where SubmitDay<=case @StartDate when '' then(select Min(SubmitDay) from temp4) else @StartDate end)),                         
  SumRemain=ISNULL((select SUM(Submit)-SUM(Confirm) from temp4 where SubmitDay<=a.SubmitDay),(select Sum(Submit)-Sum(Confirm) from temp4 where SubmitDay<=case @StartDate when '1970-01-01' then (select Min(SubmitDay) from temp4) else @StartDate end))                         
  from temp4 a group by SubmitDay                     
 )                        
 select temp4.SubmitDay,temp5.SubmitWeek,temp4.Submit,temp5.SumSubmit,temp4.Midify,temp5.SumMidify,temp4.Confirm,temp5.SumConfirm,SumRemain,temp5.WeekName                        
 from temp4 inner join temp5 on temp4.SubmitDay=temp5.SubmitDay  order by temp4.SubmitDay asc  

 

--执行结果如下: