--C7:检测数据
if @strType = 'C7'
begin
--begin
--alter table dq_项目信息 add 底盘检查检验员 varchar(30)
--end
---更新DQ项目信息
begin
DECLARE @id INT;
DECLARE @temp VARCHAR(30);
--创建游标tempCursor,并定义游标所指向的集合
DECLARE tempCursor CURSOR
FOR (select FID
from DQ_项目信息 where FID in (select Max(FID) as FID from DQ_检测信息 where len(RTrim(底盘检查检验员)) > 1 and 检测日期 >= @strDateBegin and 检测日期 <= @strDateEnd group by 车牌号码+号牌种类,检测类别) );
OPEN tempCursor; --打开游标
FETCH NEXT FROM tempCursor INTO @id; --游标读取下一个数据
WHILE @@fetch_status=0 --游标读取下一个数据的状态,0表示读取成功
BEGIN
SELECT @temp=底盘检查检验员 FROM DQ_检测信息 WHERE FID=@id; --得到本次循环的id,并查询id对应的name,赋值到@temp变量中
update DQ_项目信息 set 底盘检查检验员= @temp where FID = @id --更新DQ_项目信息表 底盘检查检验员 数据
FETCH NEXT FROM tempCursor INTO @id; --继续用游标读取下一个数据
END
close tempCursor --关闭游标
deallocate tempCursor --释放游标
end
------结束更新DQ项目信息
--01 查找数据
select top 100 percent 底盘检查检验员 AS DJY,
SUM(1) as Quantity,
SUM(case when (底盘检查_CS-底盘检查不合格次数) = '1'then 1 else 0 end) as Qualified,
SUM(case when 底盘检查_CS = '1' and 底盘检查不合格次数 = '0' then 1 else 0 end) as Qualified1,
SUM(case when 底盘检查_CS = '1' then 1 else 0 end) as Quantity1,
SUM(case when 底盘检查_CS = '2' and 底盘检查不合格次数 = '1'then 1 else 0 end) as Qualified2,
SUM(case when 底盘检查_CS = '2' then 1 else 0 end) as Quantity2,
SUM(case when 底盘检查_CS = '3' and 底盘检查不合格次数 = '2' then 1 else 0 end) as Qualified3,
SUM(case when 底盘检查_CS = '3' then 1 else 0 end) as Quantity3,
SUM(case when Convert(int,底盘检查_CS) > 3 then 1 else 0 end) as Quantity4
into #tempReportStandard_C67
from DQ_项目信息 where FID in (select Max(FID) as FID from DQ_检测信息 where len(RTrim(底盘检查检验员)) > 1 and 检测日期 >= @strDateBegin and 检测日期 <= @strDateEnd group by 车牌号码+号牌种类,检测类别)
group by 底盘检查检验员
--02 合并数据
select DJY AS DJY,
Quantity,Qualified,Qualified1,
Quantity1,
case when Quantity1 = 0 then '-' else convert(varchar(10),Qualified1*10000/Quantity1 *0.01) end as RateQualified1,
Quantity2 as QuantityRe1,
case when Quantity2 = 0 then '-' else convert(varchar(10),Qualified2*10000/Quantity2 *0.01) end as RateQualified2,
Quantity3 as QuantityRe2,
case when Quantity3 = 0 then '-' else convert(varchar(10),Qualified3*10000/Quantity3 *0.01) end as RateQualified3,
Quantity4 as QuantityRe3,
case when Quantity4 = 0 then '-' else convert(varchar(10),(Qualified-Qualified1-Qualified2-Qualified3)*10000/Quantity4*0.01) end as RateQualified4
from #tempReportStandard_C67
end