udf update

i have 1 sp, which i am executing like this.

ALTER procedure [dashboard]

----Required parameter----------------

@FromDate datetime,
     @ToDate datetime,







    as
    begin



declare @ReportData3 varbinary(max)
      exec [item3]
      -------Required Parameter--------------
      @ReportData = @ReportData3 out,

      @FromDate = @FromDate,
      @ToDate = @ToDate ,





     declare @ReportData1 varbinary(max)
      exec [item1]
      ------required parameter--------------

      @FromDate = @FromDate
      @ToDate = @ToDate,






       declare @fromYTD datetime
       declare @fromMTD datetime
       declare @FromQTD datetime
       DECLARE @FromLast1year datetime

       select @fromYTD= [APXUser].[fGetDate]('{edly}',@ToDate)
     -- print @fromYTD
        select @fromMTD= [APXUser].[fGetDate]('{edlm}',@ToDate)
       -- print @fromMTD
        select @FromQTD = [APXUser].[fGetDate]('{edlq}',@ToDate)
        --print @FromQTD
        select @FromLast1Year = DATEADD(year, -1 ,@ToDate)
        --print @FromLast1year





       create table #temp4
       (IRR float)


   declare @row1 datetime

     set @row1 = @fromYTD


    while( @row1 < @Todate)
    begin


if (   DATEADD(dd,-DAY(DATEADD(m,1,@row1)),DATEADD(m,1,@row1)) not   in(select PeriodFromDate from item 1(@ReportData1)))




         insert into #temp4
        select I1 from item3(@Reportdata3)   where
        fromdate = DATEADD(dd,-DAY(DATEADD(m,1,@row1)),DATEADD(m,1,@row1))

        set @row1 = DATEADD(MONTH ,1 , @row1)





      end



               select * from #temp4






     end

GO

i am executing this sp like this

EXEC [dashboard]

@FromDate = '09/30/2010',
@ToDate = '12/31/2011',

now my prob is , here i am passing 09/30/2010 as fromdate and 12/31/2010 as todate.

so the procedure item1 and item 3 as this as from and todate.

but in my loop i am comparing

insert into #temp4
select I1 from item3(@Reportdata3) where
fromdate = DATEADD(dd,-DAY(DATEADD(m,1,@row1)),DATEADD(m,1,@row1

now this from date is from item 3, which is another sp and the resuts is like this

item3 output

--------------

fromdate todate I1

09/30/2010 12/31/2011 4.5

if i execute my main sp like this

EXEC [dashboard]

@FromDate = '10/31/2010',
@ToDate = '12/31/2011',

the output will be like this

fromdate todate I1

10/30/2010 12/31/2011 4.5

so this comparison wont give me result as where doesnt match

what i ahve to do is the missing period from between from and todate i need pass i1 from item3 to item1.

now here row1 is date of last yera,for ex ,if i pass todate 12/31/2011 ,it will fromytd will be 12/31/2010

i need to compare this with item3 fromdate,but its udf function.i dont know how to make it possible.

coz fromdate is fixed in item.

plz help

udf update

udf update

Posts under the topic: udf update