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 - 2008-12-13 : 14:34:37
|
| I am using the following SQL query in MS SQL 2005.SELECT TOP (100) PERCENT machine, SUM(CASE WHEN lunch = 1 THEN (runtime) END) AS lunch_time, SUM(CASE WHEN paperwrk = 1 THEN (runtime) END) AS paper_work, SUM(CASE WHEN stpbar = 0 AND lunch = 0 AND paperwrk = 0 THEN (runtime) END) AS idle_time, SUM(runtime) AS totaltime, shift, jobtaskFROM dbo.ProdtrackGROUP BY machine, shift, jobtaskORDER BY machineWhat I want to do is add another column into this, the column is called T_STAMP. However when I add this column in it also wants to place it in the GROUP BY clause. When it is added to the group by clause it messes up the output generated from this query. BTW the T_STAMP column is a date/time field. If I take T_STAMP out of the GROUP BY but leave it in the SELECT it comes up with the error that it is not aggrigate or in the group by. I have tried to pick it up using commands such as MIN and MAX but the data is flawed. I know it has to be something stupidly simple I am missing but for the life of me I just can not figure it out. I really need to get this query working. Thanks for any and all assistance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-13 : 16:34:55
|
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, T_STAMP), 0)
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
|
|
Urgent Help requested. Thank you