Get rid of the weekends -- sql server reporting services

magendo_man
7/18/2006 7:19:03 AM

If you are using a stored procedure to get your dataset you could add the
following column to your select statement:

(6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
NoOfWorkingDays

You will also need to SET DATEFIRST 1 at the start of your stored procedure
to define your week start date as Monday, otherwise tha bove won't work.

HTH,
Magendo_man

Stirling, Scotland

[quoted text, click to view]

Benw
7/18/2006 7:34:02 AM

I dont think I have ever done a stored procedure before. How would I start
and where. Sorry to ask such a dumb question. I just started doing this a
couple months ago.

[quoted text, click to view]

William
7/18/2006 9:20:02 AM

In this instance you don't necessarily need a stored procedure, but it does
execute faster. Check in BOL for CREATE PROCEDURE. Essentially it is a
query in a procedure "wrapper". This allows you to pass parameters in and
optionally out. RS then uses EXEC <procedurename> @param1, @param2, ... as
its data source.

[quoted text, click to view]

William
7/18/2006 9:24:01 AM

Also note, the datefirst default is 7 (Sunday). If you change Datefirst, you
probably want to read the current value (SELECT @@DateFirst) , run your code
and then reset datefirst to its original value.

[quoted text, click to view]

magendo_man
7/18/2006 9:54:03 AM

You could past my suggested code in to your query just after
MSP_TASKS.TASK_START_DATE and before FROM MSP_PROJECTS LEFT OUTER JOIN.
However, you may have to adjust my code if your system defaults to the SQL
standard of Sunday being the first day of the week. If that is the case then
the code would, I think, have to be:

SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_START_DATE,
(7 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -2 ) AS
NoOfWorkingDays
FROM MSP_PROJECTS LEFT OUTER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID

This will give you a new column named NoOfWorkingDays in the dataset. Please
test this thoroughly before using it on a production system.

[quoted text, click to view]

Benw
7/19/2006 7:00:02 AM

wow, that worked, also. THat fixed about 4 reports. Now I have one more
request and if you dont have time, I understand. But could you take me thru
the working days formula and explain to me what it is doing. If you dont
have time, I understand. Thanks

[quoted text, click to view]

magendo_man
7/19/2006 7:37:02 AM

There are three parts to the formula:
1) Work out number of working days between start date and the end of the
week it is in
2) Work out number of whole weeks between start and finish date weeks,
multiply by 5 to get number of working days
3) Work out number of working days from beginning of the week up to the
finish date
Then add these all together.

The DATEPART(weekday, date) function gives you the number of the day in the
week between 1 and 7. In this case 1 is Sunday and 7 is Saturday. You can
change this, for example to 1 being Monday and 7 being Sunday by changing the
SQL DATEFIRST parameter in a stored procedure using SET DATEFIRST 2, which
sets Monday as the start of your week.
The DATEDIFF(week, date1, date2) function gives you the number of weeks
between date1 and date2.

HTH,
Magendo_Man

[quoted text, click to view]

Ben Watts
7/19/2006 9:44:17 AM

Thanks for the explanation, I have learned alot from this.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Get rid of the weekends