daily,wtd,mtd,ytd summarizing of data

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 2005 Forums

Transact-SQL (2005)

daily,wtd,mtd,ytd summarizing of data

Author

Topic

dchbiv
Starting Member

10 Posts

Posted - 2011-10-18 : 13:41:14

I have two tables one timecard table and a hourlyRate table

a simplified example follows:

TIMECARD
6 1234 10/1/2011 5
6 1234 10/2/2011 5
6 1234 10/3/2011 5
6 1234 10/4/2011 5
6 1234 10/5/2011 5
6 1234 10/6/2011 5
6 1234 10/7/2011 5
6 1234 10/8/2011 5
6 1234 10/9/2011 5
6 1234 10/10/2011 5
6 1234 10/11/2011 5
6 1234 10/12/2011 5
6 1234 10/13/2011 5
6 1234 10/14/2011 5
6 1234 10/15/2011 5
6 1234 10/16/2011 5
6 1234 10/17/2011 5
6 1234 10/18/2011 5
6 1234 10/19/2011 5
6 1234 10/20/2011 5
6 1234 10/21/2011 5
6 1234 10/22/2011 5
6 1234 10/23/2011 5
6 1234 10/24/2011 5
6 1234 10/25/2011 5
6 1234 10/26/2011 5
6 1234 10/27/2011 5
6 1234 10/28/2011 5
6 1234 10/29/2011 5
6 1234 10/30/2011 5
6 1234 10/31/2011 5
6 1234 11/1/2011 5
6 1234 11/2/2011 5
6 1234 11/3/2011 5
6 1234 11/4/2011 5
6 1234 11/5/2011 5
6 1234 11/6/2011 5

HOURLY_RATE
emp_no hourlyRate RateType
1234 10.74 H
2345 1400 S

I need a simplified way of summarizing the data. I need to pass a report_date parameter to the query and calculate the summary payroll for each str_id for the report_date, the weektodatefromsun, monthtodate and yeartodate.

sample output below:

str_id daily wtd mtd ytd
5 $1,400.00 $7,000.00 $37,800.00 $385,000.00
6 $1,227.00 $6,135.00 $33,129.00 $337,425.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:35:28

see

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dchbiv
Starting Member

10 Posts

Posted - 2011-10-20 : 14:23:06

I am not having much luck using the cross apply..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:35:17

why so? can we see what you tried? and error you got if any

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources