Summing per day rather than per period

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Summing per day rather than per period

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-12 : 13:51:54

I am using this query to sum payroll for a time period, but I'd also like to do it per day:

select name, employeenumber, (cast (sum (ontime) as decimal (10,2))) as minutes into scratchpad2
from scratchpad1
where date between '10/1/2010' and '10/15/2010'
group by employeenumber, name
order by employeenumber asc

Would I just use distinct here?

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 13:54:45

what is ontime?

can you post the DDL of the table, sample data and expected results?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 05:47:02

select name, employeenumber, dateadd(day,datediff(day,0,date),0) as dateonly,(cast (sum (ontime) as decimal (10,2))) as minutes into scratchpad2
from scratchpad1
where date between '10/1/2010' and '10/15/2010'
group by employeenumber, name ,dateadd(day,datediff(day,0,date),0)
order by employeenumber asc

Madhivanan

Failing to plan is Planning to fail

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-15 : 10:04:43

Madhivanan,

Thank you. That works perfectly.

Doug

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 03:02:56