81 lines
3.2 KiB
Transact-SQL
81 lines
3.2 KiB
Transact-SQL
ALTER VIEW [dbo].[V_JOINTVIEW]
|
|
|
|
AS
|
|
|
|
SELECT jot.JOT_ID,
|
|
jot.ProjectId,
|
|
WorkArea.WorkAreaId,
|
|
WorkArea.WorkAreaCode,
|
|
IsoInfo.ISO_ISONO,
|
|
jot.JOT_BelongPipe,
|
|
jot.JOT_JointNo,
|
|
jot.JOT_Dia,
|
|
jot.JOT_Sch,
|
|
jot.JOT_FactSch,
|
|
Steel.MaterialType as MetalType,
|
|
cw.wed_code AS JOT_CellWelder,
|
|
fw.wed_code AS JOT_FloorWelder,
|
|
WeldMethod.WeldingMethodName,
|
|
NDTRate.DetectionRate,
|
|
(NDTRate.DetectionRate+'%') AS NDTR,
|
|
bService.MediumName AS SER_NAME,
|
|
WeldReportMain.JOT_WeldDate,
|
|
WeldReportMain.JOT_DailyReportNo,
|
|
trust.CH_TrustCode AS CH_TRUSTCODE1,
|
|
jot.IS_Proess,
|
|
trust.CHT_CheckDate AS CHT_CHECKDATE,
|
|
CAST(ISNULL(jot.JOT_Size,0) AS DECIMAL(19,2)) AS JOT_Size,
|
|
WeldMaterialMat.ConsumablesCode AS WMT_MatCode,
|
|
WeldMaterialMat.ConsumablesName AS WMT_Matname,
|
|
WeldMaterialSilk.ConsumablesCode AS hsCode,
|
|
WeldMaterialSilk.ConsumablesName AS hsname,
|
|
jot.JOT_JointDesc,
|
|
jot.PW_PointID,
|
|
Steel.MaterialCode as STE_Name1,--材质1
|
|
(CASE WHEN jot.PW_PointID IS NULL THEN '0' ELSE '1' END) AS if_dk,
|
|
(CASE WHEN jot.IS_Proess ='1' THEN '是' ELSE '否' END) AS ProessName,
|
|
(CASE WHEN jot.PW_PointID IS NULL THEN '否' ELSE '是' END) AS if_dkName,
|
|
WorkArea.SupervisorUnitId,
|
|
jot.JOT_JointStatus,
|
|
jot.JOT_ProessDate,
|
|
HJGL_Hard_Trust.HardTrustNo HotHardCode,--硬度委托编号
|
|
HJGL_Hard_Trust.HardTrustDate HotHardDate, --硬度委托日期
|
|
HOTTrust.HotProessTrustNo JOT_HotRpt,--热处理编号
|
|
HOTTrust.ProessDate --热处理委托日期
|
|
,IsoInfo.UnitId
|
|
,trust.CH_TrustType
|
|
,trust.CH_TrustDate
|
|
,trust.CH_TrustCode
|
|
,TPPackage.PTP_TestPackageNo
|
|
FROM PW_JointInfo AS jot
|
|
LEFT JOIN PW_IsoInfo AS IsoInfo ON IsoInfo.ISO_ID = jot.ISO_ID
|
|
LEFT JOIN ProjectData_WorkArea AS WorkArea ON WorkArea.WorkAreaId = IsoInfo.WorkAreaId
|
|
LEFT JOIN Base_Material AS Steel ON Steel.MaterialId = jot.MaterialId
|
|
LEFT JOIN Base_WeldingMethod AS WeldMethod ON WeldMethod.WeldingMethodId = jot.WME_ID
|
|
LEFT JOIN Base_DetectionRate AS NDTRate ON NDTRate.DetectionRateId = IsoInfo.DetectionRateId
|
|
LEFT JOIN Base_TestMedium AS bService ON bService.TestMediumId = IsoInfo.TestMediumId
|
|
LEFT JOIN BO_WeldReportMain AS WeldReportMain ON WeldReportMain.DReportID = jot.DReportID
|
|
LEFT JOIN bs_welder AS fw ON jot.JOT_FloorWelder = fw.wed_id
|
|
LEFT JOIN bs_welder AS cw ON jot.JOT_CellWelder = cw.wed_id
|
|
LEFT JOIN Base_Consumables AS WeldMaterialMat ON WeldMaterialMat.ConsumablesId =jot.JOT_WeldMat
|
|
LEFT JOIN Base_Consumables AS WeldMaterialSilk ON WeldMaterialSilk.ConsumablesId =jot.JOT_WeldSilk
|
|
LEFT JOIN (SELECT DISTINCT JOT_ID, CH_TrustCode,CH_TrustDate ,Checks.CHT_CheckDate,CH_TrustType FROM CH_TrustItem AS TrustItem
|
|
LEFT JOIN CH_Trust AS CH_Trust ON TrustItem.CH_TrustID = CH_Trust.CH_TrustID
|
|
LEFT JOIN CH_Check AS Checks ON Checks.CH_TrustID=CH_Trust.CH_TrustID
|
|
) AS trust ON trust.JOT_ID = jot.JOT_ID
|
|
LEFT JOIN HJGL_Hard_TrustItem ON HJGL_Hard_TrustItem.WeldJointId=jot.JOT_ID
|
|
LEFT JOIN HJGL_Hard_Trust ON HJGL_Hard_Trust.HardTrustID = HJGL_Hard_TrustItem.HardTrustID
|
|
LEFT JOIN TP_IsoList TPIso ON IsoInfo.ISO_ID = TPIso.ISO_ID
|
|
LEFT JOIN TP_TestPackage TPPackage ON TPPackage.PTP_ID = TPIso.PTP_ID
|
|
LEFT JOIN HJGL_HotProess_TrustItem AS HotTrustItem ON jot.JOT_ID=HotTrustItem.WeldJointId
|
|
LEFT JOIN HJGL_HotProess_Trust AS HOTTrust ON HOTTrust.HotProessTrustId=HotTrustItem.HotProessTrustId
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|