March 12, 2002 at 12:07 pm
I wrote this procedure with OUTPUT as we still support SQL server7 to return number of business days. I looked at Steve Jones
June 2001 function to do this but it seems like if you start on sun and end on sun, it does not calculate number of business days correct.
here is an example
declare @business_days int
exec PR_num_business_days '2002-03-17', '2002-03-31', @business_days OUTPUT
print @business_days
go
The procedure I wrote seems to be working, but now I have next hurdle.. getting that value into another procedure...
/*Description:
Procedure designed to calculate the number of business days
between two dates.
*/
if exists (Select * from sysobjects where id = object_id('dbo.PR_num_business_days') AND sysstat & 0xf = 4)
drop procedure dbo.PR_num_business_days
GO
Create procedure PR_num_business_days (@start datetime, @end datetime, @business_days int OUTPUT)
as
begin
declare
@wks int
,@days int
,@sdays int
,@edays int
SET NOCOUNT ON
While datepart(dw,@start) = 1 or datepart(dw, @start)= 7
Select @start = dateadd(dd,1,@start)
While datepart(dw,@end) = 1 or datepart(dw, @end) = 7
Select @end = dateadd(dd, -1,@end)
select @days = datediff(day, @start, @End) + 1 - 2*datediff(ww, @start, @End)
select @business_days = @days
end
SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.PR_num_business_days to v5devsql
go
This is a huge procedure where I am calling pr_num_business_days but for testing I have made it small...
if exists (select * from sysobjects where id = object_id('dbo.pr_utilization') and sysstat & 0xf = 4)
drop procedure dbo.pr_utilization
GO
CREATE Procedure pr_utilization
(@startdate datetime, @Enddate datetime, @inTeamLeaderID Int, @inTrancode varchar(100),
@inbillable int, @inrole int, @inlocation int, @AssignType int, @SearchIndexID int)
with recompile as
Declare
@inTemp_Amount1 int,
@inAuthAttr_Res_ID int,
@inAuthAttr_Amount int,
@Dtloopdate datetime,
@OriginalStartDate datetime,
@OriginalFinishDate datetime,
@dailyfactor double precision,
@return_days int,
@value double precision
SET NOCOUNT ON
Select @OriginalStartDate = @startdate
Select @OriginalFinishDate = @Enddate
Select @Dtloopdate =@startdate
While datepart(dw,@startdate) = 1 or datepart(dw, @startdate)= 7
Select @startdate = dateadd(dd,1,@startdate)
While datepart(dw,@Enddate) = 1 or datepart(dw, @Enddate) = 7
Select @Enddate = dateadd(dd, -1,@Enddate)
Update mwebtemp
Set Temp_amount1 = (Select sum(AuthAttr_Amount*( select @value= exec PR_num_business_days '2002-03-17', '2002-03-31', @return_days OUTPUT))
from mwebauthattrib, mwebauth
where AuthAttr_res_id = temp_ent_ID and authattr_auth_id=auth_id and auth_status <> 70
and AuthAttr_Start_Date <= @Enddate and AuthAttr_Finish_Date >= @startdate
and AuthAttr_Amount > 0 and AuthAttr_Category = 567
and AuthAttr_Work_ID in (select Work_ID
from mwebWork
Where Work_Int_Ext7 = 10
and Work_Int_Ext8 > 0 and Work_entity_Type IN(4,30)))
Where Temp_Trancode = @inTrancode
SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.pr_utilization to v5devsql
go
I keep getting error
Server: Msg 170, Level 15, State 1, Procedure pr_utilization, Line 33
Line 33: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure pr_utilization, Line 33
Line 33: Incorrect syntax near ')'.
I changed it to Update mwebtemp
Set Temp_amount1 = (Select sum(AuthAttr_Amount*( exec PR_num_business_days '2002-03-17', '2002-03-31', @return_days OUTPUT))
from mwebauthattrib, mwebauth
where AuthAttr_res_id = temp_ent_ID and authattr_auth_id=auth_id and auth_status <> 70
and AuthAttr_Start_Date <= @Enddate and AuthAttr_Finish_Date >= @startdate
and AuthAttr_Amount > 0 and AuthAttr_Category = 567
and AuthAttr_Work_ID in (select Work_ID
from mwebWork
Where Work_Int_Ext7 = 10
and Work_Int_Ext8 > 0 and Work_entity_Type IN(4,30)))
Where Temp_Trancode = @inTrancode
I still get error at exec..
How do I do this ? Can I do this ?
Thanks for all the help
Sonali
March 15, 2002 at 1:05 am
Solali,
I've had the same problem.
I could not get the sp to work, so I ended up with a calendar table on the sugestion from somebody on a forum.
Then I wrote a small program to fill the table.
Now, whenever I need the number of b.days between two dates, I do this:
a) join my query with the calendar
b) count the number of calendar records between the two dates involved
It works, it's stable, and it is not slow.
Henrik
March 15, 2002 at 4:31 am
Sorry your first big problem is you cannot do this.
quote:
Set Temp_amount1 = (Select sum(AuthAttr_Amount*( select @value= exec PR_num_business_days '2002-03-17', '2002-03-31', @return_days OUTPUT))
You can only use EXEC SPROC as a value source for INSERTS.
Also I cannot understand you tables based on this code, can you post the DDL version of all the tables involved and how they relate to each other, or at least explain which columns go with which tables?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 22, 2002 at 4:41 pm
Your query was answered into a different thread. Anyways pasting the code here
Weekdays between two days
---------------------------------------
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Cnt INT
DECLARE @Diff INT
DECLARE @OutCnt INT
SET @StartDate = '2/1/2002' --Starting point
SET @EndDate = '3/1/2002' --Ending Point but does not count this day change WHILE @Cnt <= @Diff to count day
SET @Cnt = 0 --Set TO 0 so NULL has no effect on it.
SET @OutCnt = 0 --Set TO 0 so NULL has no effect on it.
SET @Diff = DATEDIFF(d,@StartDate, @EndDate) --Calc number of days
PRINT @Diff
WHILE @Cnt < @Diff --As long as we haven't gone that many days.
BEGIN
IF DATEPART(dw,DATEADD(d,@Cnt, @StartDate)) IN (2,3,4,5,6)
SET @OutCnt = @OutCnt + 1
SET @Cnt = @Cnt + 1 --Add one day TO incrementing value
END
PRINT @OutCnt --Output COUNT OF buiness days.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply