61 lines
2.4 KiB
Transact-SQL
61 lines
2.4 KiB
Transact-SQL
|
|
|
|
/****** Object: View [dbo].[View_IsoinfoView] Script Date: 2021/9/6 19:32:57 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
/*==============================================================*/
|
|
/* VIEW: V_IsoinfoView ¹ÜÏß×ÛºÏÐÅÏ¢ÊÓͼ */
|
|
/*==============================================================*/
|
|
ALTER VIEW [dbo].[View_IsoinfoView] AS
|
|
SELECT pw_isoinfo.ISO_ID,
|
|
pw_isoinfo.ProjectId,
|
|
PW_IsoInfo.Is_Standard,
|
|
Base_Unit.UnitName,
|
|
pw_isoinfo.iso_isono,
|
|
jot_count,
|
|
TotalDin.ISO_TotalDin,
|
|
testMedium.MediumName,
|
|
detectionRate.DetectionRate,
|
|
detectionType.DetectionTypeName,
|
|
pw_isoinfo.ISO_NDTClass,
|
|
material.MaterialType,
|
|
pw_isoinfo.ISO_Specification,
|
|
pw_isoinfo.ISO_DesignPress,
|
|
pw_isoinfo.ISO_DesignTemperature,
|
|
pw_isoinfo.ISO_TestPress,
|
|
pw_isoinfo.ISO_TestTemperature,
|
|
workArea.WorkAreaCode,
|
|
workArea.WorkAreaId,
|
|
pw_isoinfo.ISO_SysNo,
|
|
pw_isoinfo.ISO_SubSysNo,
|
|
pw_isoinfo.ISO_CwpNo,
|
|
pw_isoinfo.ISO_IsoNumber,
|
|
workArea.SupervisorUnitId,
|
|
--(SELECT TOP 1 IS_Proess FROM pw_jointinfo WHERE iso_id=pw_isoinfo.iso_id) AS is_proess,
|
|
(case when IS_Proess.IS_Proess='True' THEN 'ÊÇ' ELSE '·ñ' END)as is_proess,
|
|
pack.PTP_TestPackageNo,
|
|
CONVERT(VARCHAR(10),pack.PTP_TableDate,23) AS PTP_TableDate
|
|
FROM pw_isoinfo
|
|
LEFT JOIN Base_Unit ON Base_Unit.UnitId = pw_isoinfo.UnitId
|
|
LEFT JOIN Base_TestMedium as testMedium ON testMedium.TestMediumId=pw_isoinfo.TestMediumId
|
|
LEFT JOIN Base_DetectionRate as detectionRate ON detectionRate.DetectionRateId=pw_isoinfo.DetectionRateId
|
|
LEFT JOIN Base_DetectionType as detectionType ON detectionType.DetectionTypeId=pw_isoinfo.DetectionTypeId
|
|
LEFT JOIN Base_Material as material ON material.MaterialId = pw_isoinfo.MaterialId
|
|
LEFT JOIN ProjectData_WorkArea as workArea ON workArea.WorkAreaId = pw_isoinfo.WorkAreaId
|
|
LEFT JOIN dbo.TP_IsoList tp ON tp.ISO_ID=pw_isoinfo.ISO_ID
|
|
LEFT JOIN dbo.TP_TestPackage pack ON pack.PTP_ID=tp.PTP_ID
|
|
LEFT JOIN (SELECT COUNT(*) AS jot_count,iso_id FROM dbo.PW_JointInfo GROUP BY ISO_ID) AS jointCount ON jointCount.ISO_ID = pw_isoinfo.ISO_ID
|
|
LEFT JOIN (SELECT CAST(SUM(JOT_Size)AS DECIMAL(19,2)) AS ISO_TotalDin,ISO_ID FROM dbo.PW_JointInfo GROUP BY ISO_ID) AS TotalDin ON TotalDin.ISO_ID = pw_isoinfo.ISO_ID
|
|
LEFT JOIN (SELECT top 1 IS_Proess,ISO_ID FROM dbo.PW_JointInfo GROUP BY ISO_ID,IS_Proess) AS IS_Proess ON IS_Proess.ISO_ID = pw_isoinfo.ISO_ID
|
|
|
|
|
|
GO
|
|
|
|
|