104 lines
6.1 KiB
MySQL
104 lines
6.1 KiB
MySQL
|
|
|||
|
|
|
|||
|
|
ALTER PROC [dbo].[WeldingQualityWeekMonthReport]
|
|||
|
|
@startDate datetime = NULL,
|
|||
|
|
@endDate datetime = NULL,
|
|||
|
|
@projectType NVARCHAR(50)=NULL,
|
|||
|
|
@projectShort NVARCHAR(50)=NULL,
|
|||
|
|
@NdtType NVARCHAR(50)=NULL,
|
|||
|
|
@isClosed BIT=NULL
|
|||
|
|
AS
|
|||
|
|
|
|||
|
|
SELECT
|
|||
|
|
Project.ProjectCode as ProjectNo
|
|||
|
|
,ISNULL(Totalwelds.Totalwelds,0) AS Totalwelds
|
|||
|
|
,ISNULL(Totalwelds.DBTotal,0) as DBTotal
|
|||
|
|
,ISNULL(completedwelds.completedwelds,0) AS completedwelds
|
|||
|
|
,CAST((CAST(CASE ISNULL(Totalwelds.Totalwelds,0) WHEN 0 THEN 0
|
|||
|
|
ELSE 100.0*ISNULL(completedwelds.completedwelds,0) / (1.0 *Totalwelds.Totalwelds) END AS DECIMAL(9,1))) as nvarchar(10))+'%'
|
|||
|
|
AS completedrates
|
|||
|
|
,ISNULL(completedwelds.CumulativeDB,0) as CumulativeDB
|
|||
|
|
,CAST((ISNULL(Totalwelds.DBTotal,0)-ISNULL(completedwelds.CumulativeDB,0)) AS DECIMAL(19,3))
|
|||
|
|
AS RemainingDB
|
|||
|
|
,ISNULL(EntrustTestwelds.EntrustTestwelds,0) AS EntrustTestwelds
|
|||
|
|
,ISNULL(Testwelds.Testwelds,0) AS Testwelds
|
|||
|
|
,ISNULL(Testwelds.TestFilm,0) AS TestFilm
|
|||
|
|
,CAST((CAST(CASE ISNULL(EntrustTestwelds.EntrustTestwelds,0) WHEN 0 THEN 0
|
|||
|
|
ELSE 100.0*ISNULL(Testwelds.Testwelds,0) / (1.0 *EntrustTestwelds.EntrustTestwelds) END AS DECIMAL(9,1))) as nvarchar(10))+'%'
|
|||
|
|
AS Testrates
|
|||
|
|
,ISNULL(Rejectwelds.Rejectwelds,0) as Rejectwelds
|
|||
|
|
,CAST((CAST(CASE ISNULL(Testwelds.Testwelds,0) WHEN 0 THEN 0
|
|||
|
|
ELSE 100.0*ISNULL(Rejectwelds.Rejectwelds,0) / (1.0 *Testwelds.Testwelds-ISNULL(Rejectwelds.Rejectwelds,0)) END AS DECIMAL(9,1))) as nvarchar(10))+'%'
|
|||
|
|
AS Rejectrates
|
|||
|
|
,ISNULL(Currentcompletedwelds.Currentcompletedwelds,0) as Currentcompletedwelds
|
|||
|
|
,ISNULL(CurrentTestwelds.CurrentTestwelds,0) as CurrentTestwelds
|
|||
|
|
,ISNULL(CurrentTestwelds.CurrentTestFilm,0) AS CurrentTestFilm
|
|||
|
|
,ISNULL(CurrentRejectwelds.CurrentRejectwelds,0) as CurrentRejectwelds
|
|||
|
|
,ISNULL(CurrentRejectwelds.CurrentRejectTestFilm,0) as CurrentRejectTestFilm
|
|||
|
|
--,CAST((CAST(CASE ISNULL(CurrentTestwelds.CurrentTestwelds,0) WHEN 0 THEN 0
|
|||
|
|
-- ELSE 100.0*ISNULL(CurrentRejectwelds.CurrentRejectwelds,0) / (1.0 *CurrentTestwelds.CurrentTestwelds-ISNULL(CurrentRejectwelds.CurrentRejectwelds,0)) END AS DECIMAL(9,1))) as nvarchar(10))+'%'
|
|||
|
|
-- AS CurrentRejectrates
|
|||
|
|
,CAST((CAST(CASE WHEN ISNULL(CurrentTestwelds.CurrentTestwelds,0) = 0 THEN 0
|
|||
|
|
WHEN ISNULL(CurrentTestwelds.CurrentTestwelds,0)-ISNULL(CurrentRejectwelds.CurrentRejectwelds,0)=0 THEN 100
|
|||
|
|
ELSE 100.0*ISNULL(CurrentRejectwelds.CurrentRejectwelds,0) / (1.0 *CurrentTestwelds.CurrentTestwelds-ISNULL(CurrentRejectwelds.CurrentRejectwelds,0)) END AS DECIMAL(9,1))) as nvarchar(10))+'%'
|
|||
|
|
AS CurrentRejectrates
|
|||
|
|
FROM Base_Project AS Project
|
|||
|
|
LEFT JOIN (SELECT count(*) as Totalwelds,SUM(ISNULL(Size,0)) AS DBTotal,WeldJoint.ProjectId
|
|||
|
|
FROM Pipeline_WeldJoint AS WeldJoint
|
|||
|
|
WHERE WeldJoint.IsCancel IS NULL OR (WeldJoint.IsCancel=1 AND WeldJoint.WeldingDailyId IS NOT NULL)
|
|||
|
|
GROUP BY WeldJoint.ProjectId) AS Totalwelds ON Totalwelds.ProjectId = Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT count(*) AS completedwelds,SUM(ISNULL(Size,0)) AS CumulativeDB,WeldJoint.ProjectId
|
|||
|
|
FROM Pipeline_WeldJoint AS WeldJoint
|
|||
|
|
where WeldJoint.WeldingDailyId is not null
|
|||
|
|
GROUP BY WeldJoint.ProjectId) AS completedwelds ON completedwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT Count(*) AS EntrustTestwelds,Trust.ProjectId
|
|||
|
|
FROM Batch_BatchTrustItem AS trustItem
|
|||
|
|
left join Batch_BatchTrust as Trust on trustItem.TrustBatchId=Trust.TrustBatchId
|
|||
|
|
WHERE (Trust.DetectionTypeId=@NdtType OR @NdtType IS NULL)
|
|||
|
|
AND (trustItem.IsCancelTrust IS NULL OR trustItem.IsCancelTrust=0)
|
|||
|
|
GROUP BY Trust.ProjectId) AS EntrustTestwelds ON EntrustTestwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT Count(*) AS Testwelds,SUM(ISNULL(NDEItem.TotalFilm,0))AS TestFilm,NDE.ProjectId
|
|||
|
|
FROM Batch_NDEItem AS NDEItem
|
|||
|
|
left join Batch_NDE as NDE on NDEItem.NDEID=NDE.NDEID
|
|||
|
|
WHERE NDEItem.DetectionTypeId=@NdtType OR @NdtType IS NULL
|
|||
|
|
GROUP BY NDE.ProjectId) AS Testwelds ON Testwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT Count(*) AS Rejectwelds,NDE.ProjectId
|
|||
|
|
FROM Batch_NDEItem AS NDEItem
|
|||
|
|
left join Batch_NDE as NDE on NDEItem.NDEID=NDE.NDEID
|
|||
|
|
WHERE (NDEItem.DetectionTypeId=@NdtType OR @NdtType IS NULL) AND CheckResult='2'
|
|||
|
|
GROUP BY NDE.ProjectId) AS Rejectwelds ON Rejectwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT count(*) AS Currentcompletedwelds,WeldJoint.ProjectId
|
|||
|
|
FROM Pipeline_WeldJoint AS WeldJoint
|
|||
|
|
left join dbo.Pipeline_WeldingDaily as WeldingDaily on WeldingDaily.WeldingDailyId=WeldJoint.WeldingDailyId
|
|||
|
|
where WeldJoint.WeldingDailyId is not null
|
|||
|
|
and (@startDate is null or WeldingDate>=@startDate) and (@endDate is null or WeldingDate<=@endDate)
|
|||
|
|
GROUP BY WeldJoint.ProjectId) AS Currentcompletedwelds ON Currentcompletedwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT Count(*) AS CurrentTestwelds,SUM(ISNULL(NDEItem.TotalFilm,0))AS CurrentTestFilm,NDE.ProjectId
|
|||
|
|
FROM Batch_NDEItem AS NDEItem
|
|||
|
|
left join Batch_NDE as NDE on NDEItem.NDEID=NDE.NDEID
|
|||
|
|
where (@startDate is null or NDEDate>=@startDate) and (@endDate is null or NDEDate<=@endDate)
|
|||
|
|
AND (NDEItem.DetectionTypeId=@NdtType OR @NdtType IS NULL)
|
|||
|
|
GROUP BY NDE.ProjectId) AS CurrentTestwelds ON CurrentTestwelds.ProjectId =Project.ProjectId
|
|||
|
|
|
|||
|
|
LEFT JOIN (SELECT Count(*) AS CurrentRejectwelds,SUM(ISNULL(NDEItem.TotalFilm,0)-ISNULL(NDEItem.PassFilm,0))AS CurrentRejectTestFilm,NDE.ProjectId
|
|||
|
|
FROM Batch_NDEItem AS NDEItem
|
|||
|
|
left join Batch_NDE as NDE on NDEItem.NDEID=NDE.NDEID
|
|||
|
|
where CheckResult='2' AND (NDEItem.DetectionTypeId=@NdtType OR @NdtType IS NULL)
|
|||
|
|
and (@startDate is null or NDEDate>=@startDate) and (@endDate is null or NDEDate<=@endDate)
|
|||
|
|
GROUP BY NDE.ProjectId) AS CurrentRejectwelds ON CurrentRejectwelds.ProjectId =Project.ProjectId
|
|||
|
|
WHERE (Project.ProjectTypeId=@projectType OR @projectType IS NULL)
|
|||
|
|
AND (Project.IsClosed=@isClosed OR @isClosed IS NULL)
|
|||
|
|
AND (Project.ShortName LIKE '%'+@projectShort+'%' OR @projectShort IS NULL)
|
|||
|
|
ORDER BY Project.ProjectCode
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
|