April 16, 2009 at 1:03 pm
I am creating a report in BIDS on MS Project and want to summarize remaining work by week. Is there a way to group by a week, when the work is stored by Start Date? Also, the date defaults to working hours when storing the task, which is at 8:00. The TimebyDay has the key as the date @ 12:00am
Is there a function/view that can be used or is there another technique. On the standard Resource Availability chart, you have a choice to use Days or Weeks. I haven't found a way to produce this in the database.
Thanks in advance for any ideas.
Chris Barnes
April 17, 2009 at 6:34 am
You can use the DatePart Fucntion to get the week number and group on that. Like this in your query:
SELECT
DATEPART(week, GETDATE()) as week
This returns 16 as this is the 16th week of 2009 in my culture settings.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 17, 2009 at 6:48 am
Jack,
Thanks. The desired view of the date is by using the date range. In MS Project on the Resource view, the Resource Availability is displayed using the dates - as an example 4/12-4/18. This is what is required for the report - to break down the data into physical weeks of the calendar, and summarize the data, but show the date range in the axis.
April 17, 2009 at 7:01 am
Here's a way to do it. The date routines are from this blog post by Lynn Pettis.
SELECT
/*
0 is the first date in SQL Server so you are adding the
week part of the current date to the first day in SQL
Server which will give you the beginning of the current
week - starting Monday.
*/
dateadd(week, datediff(week, 0, GETDATE()), 0) AS week_start,
/*
Here I add 1 week to the week calculation and then subtract 1 day to
get the end of the week which in this case would be Sunday.
*/
DATEADD(DAY, -1, dateadd(week, datediff(week, 0, GETDATE()) + 1, 0)) AS week_end
Here are the results of this query:
week_start week_end
----------------------- -----------------------
2009-04-13 00:00:00.000 2009-04-19 00:00:00.000
Another option is to create a calendar table using this type of code to populate it with Day, WeekStart, WeekEnd, and any other appropriate data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply