按月、按周列举统计最近10个月 10周的数据计算

10个月的数据统计 

select a.f_year,a.f_month 
, Convert(decimal(5,2),100-100*numQualified/nullif(numFeed,0)) KpiUnqualified
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)/nullif(theoryUPH,0)) KpiOEE 
, Convert(decimal(5,2),100*numQualified/nullif( numPlanned ,0)) KpiPlanRate
, Convert(decimal(5,2),100*numWasteMater/nullif( numFeed ,0))KpiWasteMater
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)) KpiUPH
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)/nullif(numWorkers,0)) KpiUPPH
, Convert(decimal(5,2),100*numWorkWaste/nullif((numFeed-numWasteMater),0))  KpiWasteRate
--,b.targetUnqualified,b.numWasteOther,b.targetOEE,b.targetPlanRate,b.targetWasteMater,b.numPlanned,b.numQualified,b.numWasteMater,b.numWorkWaste,b.targetUPH,b.timeProduction,b.numWorkers,b.theoryUPH,b.numFeed,b.targetUPPH,b.targetWasteRate
 from (select year(F_MonthDay) f_year,month(F_MonthDay) f_month from (
SELECT  DATEADD(mm,number,dateadd(mm,-9,getdate()))  AS F_MonthDay   FROM master..spt_values WHERE TYPE = 'P' AND DATEADD(mm,number,dateadd(mm,-9,getdate())) <= getdate() )c  )a
 left join 
 (select  year(F_DataDate) f_year,month(F_DataDate) f_month
,sum(CASE F_TagCode WHEN 'targetUnqualified' THEN f_value ELSE 0 END) targetUnqualified
,sum(CASE F_TagCode WHEN 'numWasteOther' THEN f_value ELSE 0 END) numWasteOther
,sum(CASE F_TagCode WHEN 'targetOEE' THEN f_value ELSE 0 END) targetOEE
,sum(CASE F_TagCode WHEN 'targetPlanRate' THEN f_value ELSE 0 END) targetPlanRate
,sum(CASE F_TagCode WHEN 'targetWasteMater' THEN f_value ELSE 0 END) targetWasteMater
,sum(CASE F_TagCode WHEN 'numPlanned' THEN f_value ELSE 0 END) numPlanned
,sum(CASE F_TagCode WHEN 'numQualified' THEN f_value ELSE 0 END) numQualified
,sum(CASE F_TagCode WHEN 'numWasteMater' THEN f_value ELSE 0 END) numWasteMater
,sum(CASE F_TagCode WHEN 'numWorkWaste' THEN f_value ELSE 0 END) numWorkWaste
,sum(CASE F_TagCode WHEN 'targetUPH' THEN f_value ELSE 0 END) targetUPH
,sum(CASE F_TagCode WHEN 'timeProduction' THEN f_value ELSE 0 END) timeProduction
,sum(CASE F_TagCode WHEN 'numWorkers' THEN f_value ELSE 0 END) numWorkers
,sum(CASE F_TagCode WHEN 'theoryUPH' THEN f_value ELSE 0 END) theoryUPH
,sum(CASE F_TagCode WHEN 'numFeed' THEN f_value ELSE 0 END) numFeed
,sum(CASE F_TagCode WHEN 'targetUPPH' THEN f_value ELSE 0 END) targetUPPH
,sum(CASE F_TagCode WHEN 'targetWasteRate' THEN f_value ELSE 0 END) targetWasteRate
   from SE_KPI_TagValue 
 where  F_DataDate>=dateadd(mm,-9,getdate()) and F_IsLast = 1 
 group by year(F_DataDate),
 month(F_DataDate)
)b  on b.f_year=a.f_year and b.f_month = a.f_month
 order by a.f_year,a.f_month asc ; 

　　10周的数据统计

select a.f_year,a.F_Week 
, Convert(decimal(5,2),100-100*numQualified/nullif(numFeed,0)) KpiUnqualified
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)/nullif(theoryUPH,0)) KpiOEE 
, Convert(decimal(5,2),100*numQualified/nullif( numPlanned ,0)) KpiPlanRate
, Convert(decimal(5,2),100*numWasteMater/nullif( numFeed ,0))KpiWasteMater
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)) KpiUPH
, Convert(decimal(5,2),numQualified/nullif((timeProduction/60),0)/nullif(numWorkers,0)) KpiUPPH
, Convert(decimal(5,2),100*numWorkWaste/nullif((numFeed-numWasteMater),0))  KpiWasteRate
--,b.targetUnqualified,b.numWasteOther,b.targetOEE,b.targetPlanRate,b.targetWasteMater,b.numPlanned,b.numQualified,b.numWasteMater,b.numWorkWaste,b.targetUPH,b.timeProduction,b.numWorkers,b.theoryUPH,b.numFeed,b.targetUPPH,b.targetWasteRate
 from (select year(F_WeekDay) f_year,DATEPART(week, F_WeekDay)  F_Week from (
SELECT  DATEADD(wk,number,dateadd(wk,-9,getdate()))  AS F_WeekDay   FROM master..spt_values WHERE TYPE = 'P' AND DATEADD(wk,number,dateadd(wk,-9,getdate())) <= getdate() )c  )a
 left join 
 (select  year(F_DataDate) f_year,DATEPART(week, F_DataDate) F_Week
,sum(CASE F_TagCode WHEN 'targetUnqualified' THEN f_value ELSE 0 END) targetUnqualified
,sum(CASE F_TagCode WHEN 'numWasteOther' THEN f_value ELSE 0 END) numWasteOther
,sum(CASE F_TagCode WHEN 'targetOEE' THEN f_value ELSE 0 END) targetOEE
,sum(CASE F_TagCode WHEN 'targetPlanRate' THEN f_value ELSE 0 END) targetPlanRate
,sum(CASE F_TagCode WHEN 'targetWasteMater' THEN f_value ELSE 0 END) targetWasteMater
,sum(CASE F_TagCode WHEN 'numPlanned' THEN f_value ELSE 0 END) numPlanned
,sum(CASE F_TagCode WHEN 'numQualified' THEN f_value ELSE 0 END) numQualified
,sum(CASE F_TagCode WHEN 'numWasteMater' THEN f_value ELSE 0 END) numWasteMater
,sum(CASE F_TagCode WHEN 'numWorkWaste' THEN f_value ELSE 0 END) numWorkWaste
,sum(CASE F_TagCode WHEN 'targetUPH' THEN f_value ELSE 0 END) targetUPH
,sum(CASE F_TagCode WHEN 'timeProduction' THEN f_value ELSE 0 END) timeProduction
,sum(CASE F_TagCode WHEN 'numWorkers' THEN f_value ELSE 0 END) numWorkers
,sum(CASE F_TagCode WHEN 'theoryUPH' THEN f_value ELSE 0 END) theoryUPH
,sum(CASE F_TagCode WHEN 'numFeed' THEN f_value ELSE 0 END) numFeed
,sum(CASE F_TagCode WHEN 'targetUPPH' THEN f_value ELSE 0 END) targetUPPH
,sum(CASE F_TagCode WHEN 'targetWasteRate' THEN f_value ELSE 0 END) targetWasteRate
   from SE_KPI_TagValue 
 where  F_DataDate>=dateadd(wk,-9,getdate())  and F_IsLast = 1 
 GROUP BY  year(F_DataDate) ,DATEPART(week, F_DataDate)
)b  on b.f_year=a.f_year and b.F_Week = a.F_Week
 order by a.f_year,a.F_Week asc ;