生产周报表

代码示例

DECLARE @s nvarchar(MAX);
SET @s = ''
DECLARE @dte1 datetime
DECLARE @dte2 datetime
DECLARE @int int
SELECT @int = COUNT(*)
FROM owor p0
WHERE p0.duedate >= '[%0]'
	AND p0.duedate <= '[%1]'
SET @dte1 = '[%0]'
SET @dte2 = '[%1]'
SELECT @s = @s + ',sum(case when t0.duedate=''' + CAST(duedate AS nvarchar(20)) + ''' then t0.PlannedQty else 0 end) as [' + CAST(month(duedate) AS nvarchar(2)) + '/' + CAST(day(duedate) AS nvarchar(2)) + N'计划],' + 'sum(case when t0.duedate=''' + CAST(duedate AS nvarchar(20)) + ''' then t0.CmpltQty else 0 end) as [' + CAST(month(duedate) AS nvarchar(2)) + '/' + CAST(day(duedate) AS nvarchar(2)) + N'良品],' + 'sum(case when t0.duedate=''' + CAST(duedate AS nvarchar(20)) + ''' then t0.RjctQty else 0 end) as [' + CAST(month(duedate) AS nvarchar(2)) + '/' + CAST(day(duedate) AS nvarchar(2)) + N'次品]'
FROM owor
WHERE DueDate >= @dte1
	AND DueDate <= @dte2
GROUP BY duedate
ORDER BY duedate
SET @s = 'select t0.itemcode as 物料,t1.itemname as 描述,sum(t0.PlannedQty) as [计划总数]' + @s + ',sum(t0.CmpltQty) as [总计良品],sum(t0.RjctQty) as [总计次品],sum(t0.CmpltQty+t0.RjctQty) as [合计生产],sum(t0.CmpltQty+t0.RjctQty)-sum(t0.PlannedQty) as [差异],sum(t0.CmpltQty+t0.RjctQty)/sum(t0.PlannedQty)*100 as [完成率(%)],case when sum(t0.CmpltQty+t0.RjctQty)=0 then 0 else sum(t0.CmpltQty)/sum(t0.CmpltQty+t0.RjctQty)*100 end as [合格率(%)] from owor t0 inner join oitm t1 on t0.itemcode=t1.itemcode where (t0.duedate>= ''' + CAST(@dte1 AS nvarchar(20)) + ''') AND (t0.DueDate <= ''' + CAST(@dte2 AS nvarchar(20)) + ''') group by t0.itemcode,t1.itemname'
EXEC  (@s)