November 4, 2010 at 1:24 pm
I'm trying to sum a column as well as round a column up to the nearest minute. Here's 2 queries I'm running, but I'd like to combine them in to one. Can anyone offer any suggestions:
Query 1
select name, employeenumber, sum (ontime) as totalminutes into scratchpad2
from scratchpad1
where date between '5/1/2010' and '5/15/2010'
group by employeenumber, name
order by employeenumber asc
Query 2
select totalminutes, (cast (totalminutes as decimal (10,2))) as realtime
from scratchpad1
Thank you
Doug
November 4, 2010 at 1:40 pm
There is a Round() function. You should be able to wrap that around the Sum() function in your first query. Have you tried that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 2:07 pm
Gsqaured,
I dont think that the round function would work unless it's after I do the cast, because it is in a decimal format before that. Can you show me an example of what you mean though?
Thanks
Doug
November 4, 2010 at 2:23 pm
I guess I don't understand what you're asking.
What type of data does your "ontime" column contain? What does the sum of it look like?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 3:34 pm
G
The ontime column contains data that looks like this:
15.01015
and what I need to do is to round it to the nearest minute. I have to use a conversion (([LoggedIn]/1000/60) to get this number ... but thats in an earlier query.
Does that help?
November 5, 2010 at 6:25 am
And what does it contain? The number of seconds they were online? Minutes and fractions thereof?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 7:58 am
Yes, its recorded in 15 minute increments. It's minutes and seconds.
November 8, 2010 at 6:26 am
So if the sum ends up at 15.01015, and you want to round to the nearest minute, how does "round(15.01015, 0)" not get you what you need? It'll return "15". It'll return 16 for 15.5001, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply