Nested Query-Sequential Dates Of Business Processes

• raja_saminathan

  SSCommitted

  Points: 1607

  More actions

  June 9, 2008 at 10:23 pm

  #826431

  Hi ,

  Can we use a function for this solution or this will give poor performance,can nybody suggest where iam wrong?

  create function DateCalc(@processID int)

  returns Datetime

  as

  begin

  Declare @Date DateTime,@Deadline DateTime,@PRocessAfter int

  select @ProcessAfter = cast(ProcessAfter as int) from ProcessTest1 where processId = @ProcessId

  if (@ProcessId ='001')

  begin

  set @deadLine = getDate();

  end

  else

  begin

  set @DeadLIne = (select DeadLine from t where processId = @ProcessAfter)

  if (@DeadLine is null)

  set @DeadLine = getDate();

  end

  set @Date = DateADD(dd,(select Daycount from ProcessTest1 where ProcessID= @ProcessID),@DeadLine)

  return @Date

  end

  --

  create Table t (ProcessId int,DeadLine DateTime)

  insert into t

  select ProcessId,dbo.dateCalc(ProcessID) from ProcessTest1

  select * from ProcessTest1

  select * from t

  The Result is

  12008-06-22 09:48:31.063

  22008-06-11 09:48:31.080

  32008-06-12 09:48:31.080

  42008-06-12 09:48:31.080

  52008-06-14 09:48:31.080

  62008-06-14 09:48:31.080

  I am trying to get the Desired Result and i didn't understand where i went wrong

  rajesh