Get rid of the weekends

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
FROM MSP_PROJECTS LEFT OUTER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
MSP_TASKS.TASK_START_DATE"
Using my fields how would I exclude the weekends from this.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
"Benw" wrote:
> 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
> FROM MSP_PROJECTS LEFT OUTER JOIN
> MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> MSP_TASKS.TASK_START_DATE"
> Using my fields how would I exclude the weekends from this.|||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.
"magendo_man" wrote:
> 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
>
> "Benw" wrote:
> > 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
> > FROM MSP_PROJECTS LEFT OUTER JOIN
> > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> >
> > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > MSP_TASKS.TASK_START_DATE"
> >
> > Using my fields how would I exclude the weekends from this.|||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.
"Benw" wrote:
> 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.
> "magendo_man" wrote:
> > 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
> >
> >
> > "Benw" wrote:
> >
> > > 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
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||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.
"Benw" wrote:
> 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.
> "magendo_man" wrote:
> > 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
> >
> >
> > "Benw" wrote:
> >
> > > 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
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||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.
"Benw" wrote:
> 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.
> "magendo_man" wrote:
> > 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
> >
> >
> > "Benw" wrote:
> >
> > > 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
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||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
"magendo_man" wrote:
> 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.
>
> "Benw" wrote:
> > 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.
> >
> > "magendo_man" wrote:
> >
> > > 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
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > 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
> > > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > > >
> > > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > > MSP_TASKS.TASK_START_DATE"
> > > >
> > > > Using my fields how would I exclude the weekends from this.|||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
"Benw" wrote:
> 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
> "magendo_man" wrote:
> > 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.
> >
> >
> > "Benw" wrote:
> >
> > > 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.
> > >
> > > "magendo_man" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > 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
> > > > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > > > >
> > > > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > > > MSP_TASKS.TASK_START_DATE"
> > > > >
> > > > > Using my fields how would I exclude the weekends from this.|||Thanks for the explanation, I have learned alot from this.
"Benw" <Benw@.discussions.microsoft.com> wrote in message
news:2A0A983A-53E9-49CC-8CF5-9FBDB331F518@.microsoft.com...
> 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
> FROM MSP_PROJECTS LEFT OUTER JOIN
> MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> MSP_TASKS.TASK_START_DATE"
> Using my fields how would I exclude the weekends from this.