16 lines
1.6 KiB
SQL
16 lines
1.6 KiB
SQL
select a.UnitId,b.UnitName,c.WorkAreaId,c.CH_NDTMethod,c.TrustTotalNum,e.DetectionTypeName,d.CheckTotalNum,c.trustTotalNum-d.CheckTotalNum NotCheck,100*d.CheckTotalNum/c.trustTotalNum HasPer from Project_ProjectUnit a
|
|
left join Base_Unit b on a.UnitId=b.UnitId
|
|
left join (
|
|
select a.CH_CheckUnit,a.CH_NDTMethod,WorkAreaId,count(distinct b.JOT_ID)TrustTotalNum from CH_Trust a left join CH_TrustItem b on a.CH_TrustID=b.CH_TrustID
|
|
LEFT JOIN pw_jointinfo c on c.JOT_ID=b.JOT_ID LEFT JOIN pw_isoinfo ON c.ISO_ID = pw_isoinfo.ISO_ID
|
|
where a.ProjectId=@ProjectId and a.CH_AuditDate is not null and (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard ='')
|
|
group by a.CH_CheckUnit,a.CH_NDTMethod,WorkAreaId)
|
|
c on a.UnitId = c.CH_CheckUnit
|
|
left join (
|
|
select a.CH_CheckUnit,WorkAreaId,a.CH_NDTMethod,count(distinct c.JOT_ID)CheckTotalNum from CH_Trust a left join CH_Check b on a.CH_TrustID=b.CH_TrustID
|
|
left join CH_CheckItem c on b.CHT_CheckID=c.CHT_CheckID LEFT JOIN pw_jointinfo d on d.JOT_ID=c.JOT_ID LEFT JOIN pw_isoinfo ON d.ISO_ID = pw_isoinfo.ISO_ID
|
|
where a.ProjectId=@ProjectId and a.CH_AuditDate is not null and b.CHT_AuditDate is not null and (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard ='')
|
|
group by a.CH_CheckUnit ,a.CH_NDTMethod ,WorkAreaId
|
|
)d on a.UnitId = d.CH_CheckUnit and d.CH_NDTMethod = c.CH_NDTMethod and c.WorkAreaId = d.WorkAreaId
|
|
left join Base_DetectionType e on e.DetectionTypeId = c.CH_NDTMethod
|
|
where a.UnitType = '6' and a.ProjectId=@ProjectId |