SQL Calculation...
-
Hi, Can someone please advise me on what to do with this issue. I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M My issue is this: I have 2 lines on the same day. 2010/01/05 1HOUR 30MINUTES 90MINUTES 2010/01/05 2HOURS 20MINUTES 140MINUTES How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals? 2010/01/05 230MINUTES My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
-
Hi, Can someone please advise me on what to do with this issue. I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M My issue is this: I have 2 lines on the same day. 2010/01/05 1HOUR 30MINUTES 90MINUTES 2010/01/05 2HOURS 20MINUTES 140MINUTES How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals? 2010/01/05 230MINUTES My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
You need to remove GrossHours and GrossMinutes from your select statement and group by.
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!