Grouping Daily dates to weekly date format...

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)

Grouping Daily dates to weekly date format...

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-27 : 05:54:35

Hi there.

I've been looking at this for a while and still can't work it all out. This looks quite long so please bare with me.

I'm using an Access frontend, and, more to the point, it has linked tables from two SQL Server dbs.

The Access db is designed to keep track of Invoices that will be forwarded to agencies for billing.

For the sake of trying to keep this thread simple, the relevant fields are to do with how many hours’ staff work in a week.

The two SQL Server dbs are Agency and Staff. The staff db is for all staff regardless if agency or not. The Agency db is only for Agency staff time sheets, this part is filled into the Access db while the other db has it’s own frontend.

Both dbs have fields that ascertain how many hours each staff work each week. However, The Staff db's date and hour fields are daily and the Agency db’s date and hour fields are weekly. The agency staff fill out fields from both dbs.

Here's the problem

On my access frontend I have a continuous form. Each line pulls data from both dbs staff date and Hour fields. This is so Users can compare any differences in hours from both dbs.

the dbs relevant tables looks like this

Staff db table
-----------
DateWorked (DateTime) Daily Date
Hours (Number) Daily hours

Agency db Table
------------
ID (Autonumber)
WeekEndingDate (DateTime)
TotalHours (Number)

So, the Staff table, being Daily values would show:
27/Aug/2008 – 7.25 Hours

But the Agency table, Being Weekly values would show:
22/Aug/2008 - 36.25 Hours
(Which is for the whole week and the date groups hours from the 7 previous days.

My question is, is there any way, function, method that can manipulate the staff table so that it will group the Daily dates into weekly dates and subsequently group the hours so that it’s the sum of hours for that whole week?

I’d be using the fields - StaffEmployee and Weekendingdate as keys to reference both tables in MS Access and they have to be in the same weekly format so that users can compare any differences with the hours from both dbs.

Hope someone can help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:04:08

Try this out

SELECT DATEADD(wk,DATEDIFF(wk,0,DateWorked),0),
SUM(Hours)
FROM Staff
GROUP BY DATEADD(wk,DATEDIFF(wk,0,DateWorked),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources