CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Soham Shah
Soham Shah

Reputation: 571

Shift Report for Week / Month for a particular shift 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.

Also, for 3rd shift the time will be 10 PM of the start date and 6 AM of startdtae + 1.

How can I achieve that? I'm really confused and stuck.

Thanks

Upvotes: 0

Views: 900

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Use the DATEPART() function in SQL to add another condition that checks that the hour of the DT_CONFIRMATION column is within the hours of the shift you are filtering on.

So if you were searching for only Shift 1, you would add:

AND DATEPART(hour, DT_CONFIRMATION) BETWEEN 6 AND 14

to your WHERE clause. You will obviously need to parameterize the WHERE clause with hours based on the Shift, the same as you have done with the dates.

Upvotes: 1

Related Questions