In my UI, I have two filters:
Date - Today , Yesterday, WTD , MTD
Shift - 1st, 2nd, 3rd
Shift timings are following:
1st: 06:00 - 14:00
2nd: 14:00 - 22:00
3rd: 22:00 - 06:00
I am counting production qualtities for each material via following query:
SELECT
C.MATERIAL
,G.MACHINE
,F.PART
,SUM(A.QT_CONFIRMED) QT_CONFIRMED
FROM
CONFIRMATION A
INNER JOIN LOT_RECORD B ON A.LOT_RECORD_ID=B.LOT_RECORD_ID
INNER JOIN LOT C ON B.LOT_RECORD_ID=C.LOT_RECORD_ID
INNER JOIN MATERIAL D ON C.MATERIAL = D.MATERIAL
INNER JOIN PART F ON C.PART=F.PART
INNER JOIN MACHINE G ON F.MACHINE=G.MACHINE
WHERE
A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]'
GROUP BY
C.MATERIAL
,G.MACHINE
,F.PART
ORDER BY
G.MACHINE ASC
For Today / Yesterday, I can get the date and shift and can pass relevant parameter to the query and getting proper output.
Like if I select Yesterday and Choose shift 2nd then Param.1 = 2015-12-14T14:00:00 Param.2 = 2015-12-14T22:00:00
But problem I'm facing is for WTD and MTD. If I choose MTD and 1st shift then
My Start date will be 1st Dec. End Date will be 15th Dec.
Now I can't pass
Param.1 = 2015-12-01T06:00:00
Param.2 = 2015-12-15T14:00:00
Because it will calculate quantity for all the shifts. I need to pass parameter in such a way that it calulates quantity only between 06:00 - 14:00 for 1st Dec to 15th Dec.
How can I achieve that? I'm really confused and stuck.
Thanks
Aucun commentaire:
Enregistrer un commentaire