1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
|
SELECT DISTINCT dateend, active,
PT.idpart, PT.Namepart,
COALESCE(SUM(TC1.nb), 0) AS review,
COALESCE(SUM(TC2.nb), 0) AS reject,
COALESCE(DATEDIFF(day, GETDATE(), MAX(TC3.DateInsp)),
DATEDIFF(day, GETDATE(), PJ.datestart)) AS jour
FROM Tparts PT
INNER JOIN TPartQC QC
ON PT.idpart = QC.part
INNER JOIN TEChart TC1
ON TC1.part = PT.idpart
INNER JOIN TEChart TC2
ON TC2.part = PT.idpart
AND TC2.qc = QC.qc
INNER JOIN TEChart TC3
ON TC3.part = PT.idpart
INNER JOIN Tproject PJ
ON PT.project = PJ.idproject
WHERE PT.project = @project
and QC.qc = @concern
and TC1.qc =0
and TC1.DateInsp BETWEEN @start and @end
and TC2.DateInsp BETWEEN @start and @end
and TC3.QC <> 0
GROUP BY TC3.part, dateend, active, PT.idpart, PT.Namepart, TC3.DateInsp, PJ.datestart
order by reject DESC, active DESC, review DESC |
Partager