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/


Go to Top of Page

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
Go to Top of Page

dougancil
Posting Yak Master

217 Posts

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

Thank you. That works perfectly.

Doug
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 03:02:56
quote:
Originally posted by dougancil

Madhivanan,

Thank you. That works perfectly.

Doug


You are welcome

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -