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.
| Author |
Topic |
|
cidr2
Starting Member
28 Posts |
 Posted - 2008-07-30 : 03:56:25
|
| Hi there, first time in the forum so I hope everyone's well.I wonder if I can ask a question. I'm working on a db that has already been developed as well as the frontend. it's to store employee info i.e. hours worked on what date etc.The hours are recorded daily so somthing like this:SQL table.Field: DateWorked (Date/Time) 30/07/08Field: HoursWorked: (Number) 7.25 Now, I want to ask if anyone knows if there's a function or way in SQL Server 2005 where I can group the Daily dates into week dates, So, for example, I'd be taking the Dates from:Monday(21/07/08) to Friday(25/07/08)And grouping them together, making the 25th the weekending date. This is so I can add the sum of daily hours worked inbetween these dates and I'll be doing that for all future dates and all past dates from the start of 2008, As long as I have the functionality to make every friday the weekending date I'm golden.Just to note. I'm doing this because of another db that pulls the data from the one mentioned.I've been looking at functions i.e. week function, but I'm not really sure how to implement that.Any help would be great |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-30 : 06:21:58
|
[code]SELECT WeekDate = DATEADD(DAY, 4, WeekDate), HoursWorkedFROM( SELECT WeekDate = DATEADD(week, DATEDIFF(week, 0, DateWorked), 0), HoursWorked = SUM(HoursWorked) FROM [TABLE] GROUP BY DATEADD(week, DATEDIFF(week, 0, DateWorked), 0)) d[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
|
|
sum daily date to a week date...