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 |
|
timgaunt
Posting Yak Master
115 Posts |
 Posted - 2007-01-16 : 14:28:27
|
| Evening all!I'm just wondering which people would opt for in this situation:I've got a list of timesheets which are submitted on a weekly basis, what I would like to do is generate a list of weeks that the user hasn't submitted a timesheet. I can work out the week commencing date but do I:a. Store a list of the week commencing dates in another table -this could be dynamically created as we enter a new weekb. Do it dynamically some how (and just filter from the system start date)Thoughts?TIATimps if you want to know how to calculate the week commencing you can use this:DATEADD(day, DATEDIFF(day, 0, DATEADD(day, - (1 * DATEPART(dw, GETDATE())) + 2, GETDATE())), 0) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-16 : 14:36:25
|
| Option a, but modified: create this table once, starting from your earliest needed date until a date far into the future, say 2100 or something like that. index it up, add more columns to it if that is helpful (year, month, week number, etc for each date -- storing the data and indexing it is quicker than calculations) and there you go. A simple select FROM the dates table and an OUTER JOIN to the timesheets table does the job. Even better, once the timesheet dates table is created, for proper referential integrity, make your timesheets' date column reference the dates in that table. - Jeff |
 |
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2007-01-16 : 14:44:30
|
| Thanks John, I was thinking about creating it and then indexing and can see the advantage of doing it till i.e. 2100 -if it's still in operation then I'll just have to add some more ;)Tim*Feeling pleased someone agrees with him for once!*----------------------------I've finally started blogging (all be it badly!)Check it out:http://blogs.thesitedoctor.co.uk/tim/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-16 : 14:48:12
|
quote:
Originally posted by timgaunt
Thanks John, I was thinking about creating it and then indexing and can see the advantage of doing it till i.e. 2100 -if it's still in operation then I'll just have to add some more ;)Tim*Feeling pleased someone agrees with him for once!*----------------------------I've finally started blogging (all be it badly!)Check it out:http://blogs.thesitedoctor.co.uk/tim/
You can use th function on this link to create a date table:Date Table Function F_TABLE_DATEhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519It already has week start dates in it, as well as many other features.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Opinions Please: Outputting past weeks