ALTER VIEW [dbo].[HJGL_View_CheckResultSummary]
/*无损检测结果汇总表*/
AS

SELECT c.*,
 (CASE WHEN CHARINDEX('20d2cbca-8b3d-434b-b1c1-181796986fa5',c.NDT)>0 THEN

 (SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
 WHERE report.CH_NDTMethod='20d2cbca-8b3d-434b-b1c1-181796986fa5' AND report.BatchId=c.BatchId
 ORDER BY report.CH_TrustCode)
 ELSE  NULL  END) AS RTReport,

 (CASE WHEN CHARINDEX('20d2cbca-8b3d-434b-b1c1-181796986fa5',c.NDT)>0 THEN
 (SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item 
 LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID 
 WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='20d2cbca-8b3d-434b-b1c1-181796986fa5' AND item.States IN ('2','4'))
 ELSE NULL end) AS RT ,

 
 (CASE WHEN CHARINDEX('ee316c8c-9899-4ab8-8152-7f00b655243d',c.NDT)>0 THEN
 (SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
 WHERE report.CH_NDTMethod='ee316c8c-9899-4ab8-8152-7f00b655243d' AND report.BatchId=c.BatchId
 ORDER BY report.CH_TrustCode)
 ELSE  NULL  END) AS UTReport,

 (CASE WHEN CHARINDEX('ee316c8c-9899-4ab8-8152-7f00b655243d',c.NDT)>0 THEN
 (SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item 
 LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID 
 WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='ee316c8c-9899-4ab8-8152-7f00b655243d' AND item.States IN ('2','4'))
 ELSE NULL end) AS UT ,
 
 (CASE WHEN  CHARINDEX('6bac78b9-0e82-4885-bb8f-36486b15e545',c.NDT)>0 THEN
 (SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
 WHERE report.CH_NDTMethod='6bac78b9-0e82-4885-bb8f-36486b15e545' AND report.BatchId=c.BatchId
 ORDER BY report.CH_TrustCode)
 ELSE  NULL  END) AS MTReport,

 (CASE WHEN CHARINDEX('6bac78b9-0e82-4885-bb8f-36486b15e545',c.NDT)>0 THEN
   (SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item 
      LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID 
    WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='6bac78b9-0e82-4885-bb8f-36486b15e545' AND item.States IN ('2','4'))
  ELSE NULL end) AS MT, 
 
 (CASE WHEN CHARINDEX('3799c68a-895e-4ed1-b516-dbb487660832',c.NDT)>0 THEN
 (SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
 WHERE report.CH_NDTMethod='3799c68a-895e-4ed1-b516-dbb487660832' AND report.BatchId=c.BatchId
 ORDER BY report.CH_TrustCode)
 ELSE  NULL  END) AS PTReport,

  (CASE WHEN CHARINDEX('3799c68a-895e-4ed1-b516-dbb487660832',c.NDT)>0 THEN
   (SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item 
      LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID 
    WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='3799c68a-895e-4ed1-b516-dbb487660832' AND item.States IN ('2','4'))
  ELSE NULL end) AS PT
 
FROM dbo.HJGL_View_CheckResultSummary1 c



GO