mardi 15 décembre 2015

Month to Date / Week To Date AND Shift filter for Shift Report in SQL

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