Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2009-01-24 : 21:21:49
|
| I am in the process of writing a Query in SQL but am having trouble when the query hits the midnight hour. I am doing a suming and totaling on a table and I want it to seperate things by shift, machine location, and then sum the various fields. Here is what I have so far:select machine, jobtask, shift, sum(counter), sum(runtime) from table1 where datediff(d,t_stamp,current_timestamp)=0 group by machine, shift, jobtaskThe problem is the way our shifts are laid out. Under normal circumstances which occurs about 75% of the time the shifts run 6:30am-3:00pm, 4:30pm-1am and 10pm-6:30am. However when business is good we have split shifts all over the place. part of 1st will work 4am till 4:30pm while the other part works 6:30a-6:00p and the same kind of variations hold true on all the other shifts as well. What I am trying to do is find a way to sum and show only that days production totals. So say that 2nd was at normal time I only want anything from 4:30pm but do not want what was ran after midnight on the current day but do want to include everything thru midnight the next day. Hope this is clear.The followng are the fields in the table:machine - intt_stamp - datetime (a record that is written when that row is written)counter - int (a value that has been run for a period of time)shift - int (value is written as part of data stream when that person logs in) can be 1, 2, or 3runtime - int (value of span of time)there are others as well but most are 1's and 0's and are not relevant.Thanks and have a great day.Any questions just ask. I will try my best to answer in a way that will hopefully make things clear. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 04:42:27
|
| you may be better off explaining this with some data to illustrate what you want. |
 |
|
|
|
|
|
|
|
Query assistance for midnight hour