Procedure help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure help

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-06-04 : 12:57:13

I have the following

declare @ActualDate datetime
declare @importdate datetime
declare @DateDiff int
set @DateDiff=datediff(day,@StartDate,@EndDate)
DECLARE @rtDayofWeek VARCHAR(10)

set @DateDiff=@DateDiff+1

while @DateDiff>0
begin

set @ActualDate=@StartDate
set @rtDayofWeek =DATEPART(weekday,@StartDate)
if @rtDayofWeek>=2 and @rtDayofWeek<=5 --Monday to thursday
begin
set @StartDate=dateadd(day,1,@StartDate)
set @importdate=@StartDate
set @DateDiff=@DateDiff-1
insert into @TempOBRMasterData select *,OBR_10_bundle+OBR_Blackberry as 'Total' from Fn_GetOBRMasterDataByimportdate(@importdate,@ActualDate)
end
else
begin
if @rtDayofWeek=6 --Frieday
begin
set @importdate=dateadd(day,3,@StartDate)
insert into @TempOBRMasterData select *,OBR_10_bundle+OBR_Blackberry as 'Total' from Fn_GetOBRMasterDataByimportdateFriday(@importdate,@ActualDate)
set @StartDate=dateadd(day,1,@StartDate)
set @DateDiff=@DateDif

I am having trouble with the dates. The way I run the procedure is :

exec procedure 'date beginning','date ending'

How can I set my procedure for friday where day of week = 6 so that
set @StartDate=dateadd(day,0,@StartDate)

instead of dateadd(day,1,@startdate) because when I change it to 0 i get errors.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-04 : 13:52:33

Can you explain your actual requirement. Why are you adding one day if its Monday to Thurs and adding 3 if its a Friday. I dont get the logic.

What should the values of @importdate and @actualdate be?

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-06-04 : 14:15:28

importdate is 1 day ahead of actual date when the day is from monday-thursday but when the day is friday or saturday the importdate is the following monday. So when i run the procedure, if i put in 05/04/2010, that would actually run importdate of 5/5/2010 so thats why the startdate is dateadd 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-04 : 16:01:33

This should do, no?

declare @startdate datetime
declare @importdate datetime
set @startdate = '20100603'
--set @startdate = '20100604'--Set Different values for @startdate if needed for testing

set @importdate = 
case when datepart(weekday,@startdate) = 6 then dateadd(day,3,@startdate) 
else dateadd(day,1,@startdate)
end

insert into @TempOBRMasterData 
select *,OBR_10_bundle+OBR_Blackberry as 'Total' 
from Fn_GetOBRMasterDataByimportdate(@importdate,@startdate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources