fetching date from 1 sp to another

  • coz while executing sp, i am passing fromdate and todate.

    so its taking only that date, in both function

  • hbtkp (4/13/2012)


    OK,

    BASICALLY PITEM AND PITEM4 Calling function fitem and fitem4

    now this two function item has data for some fromdate and to date and values

    item4 has fromdate,todate and vales,but for all date.

    while in item4 function has some date ,that r missing in item function.

    so i m trying to get that mising date and based on values to insert in item function.

    using

    DECLARE @DATE1 DATETIME

    SELECT @DATE1 = ThruDate

    FROM fitem(@ReportData1)

    INSERT INTO fitem4 (@ReportData1)(IRRReal)

    SELECT IRR

    FROM fitem(@ReportData1) pb

    WHERE @DATE1 = pb.FromDate

    AND ThruDate = DATEADD(MONTH, 1, @DATE1)

    but ite not working

    finally exactly what you are trying to do. i think you have a severe missunderstanding of what a user defined function is. here is the BOL article on user defined functions. http://msdn.microsoft.com/en-us/library/aa214363(v=sql.80).aspx read that and if you still think you are doing something that is possible we have a bigger problem.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Again, you cannot insert into a function.

    Insert the missing dates into the base table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok.

    just tell me logic

    how can i get if this dates r missing in field

  • hbtkp (4/13/2012)


    ok.

    just tell me logic

    how can i get if this dates r missing in field

    you need to add the missing dates to the data source. if the dates are not in the tables you can not get them out of the tables. so if you are missing dates make sure they are actually in the table you are pulling from. if they are not you are not really missing them since they are not there to be missed.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Missing in that you have column with dates and you want to see which dates are not represented in the table.

    First thing you need for that is a calendar table (http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx, http://www.sqlservercentral.com/articles/T-SQL/70482/)

    Once you have that calendar table, it's a trivial outer join between the calendar table and your table that contains dates

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok.

    this dates are in 1 udf and i need to add in another udf.

    let me explain you.

    (1) i am executing sp , and am passing param @todate

    (2) now i need to calculate fromdate ,that i m calling 1 udf ,and getting startdate

    (3) so i have start and to date.now i need to compare this two with 2 fiels in udf ,which i s from1,start1

    if its not their , i need to get it from another udf and put it into this udf.

    this is sampel of my sp

    create stored procedure sp(

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

    @FromDate datetime,

    @ToDate datetime,

    declare @ReportData3 varbinary(max)

    EXEC ITEM

    @ReportData = @ReportData3,

    @FromDate = @FromDate,

    @ToDate = @ToDate

    declare @ReportData1 varbinary(max)

    exec item3

    @ReportData = @ReportData3,

    @ReportData = @ReportData3,

    @FromDate = @FromDate,

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

    --now this sp calling function whicxh has value function name - fitem and fitem3

    i need to compare my fromdate and todate with the field item to and fromdate,if it is missing i need fetch it from fitem3 and put it int fitem.

    plz help

    @ToDate = @ToDate

  • help me with this code

    its not showind date

    declare @row1 datetime

    set @row1 = @fromYTD

    while(@row1 = @Todate)

    begin

    select thrudate from item3

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

    end

    plz help me

  • hbtkp (4/13/2012)


    ok.

    this dates are in 1 udf and i need to add in another udf.

    Once more with feeling...

    You cannot add data to a udf. A udf is just a query against a table, it does not contain data and it cannot have data inserted into it. You need to insert data into the base table that the udf queries or you need to change the udf so that it returns the data that you need,

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • hbtkp (4/13/2012)


    help me with this code

    its not showind date

    declare @row1 datetime

    set @row1 = @fromYTD

    while(@row1 = @Todate)

    begin

    select thrudate from item3

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

    end

    What is this supposed to do?

    What is it currently doing?

    What are the values of @fromYTD and @Todate?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • fromdate suppuse = 10-30-2010

    todate = 10 - 30- 2011

    i need to check wheather this date is there in item2 function

  • What's the definition of the function item2?

    What does it return, a single value or a resultset?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • result set ,and it has 2 fileds (fromdate and todate)

  • simple logic,

    suppose i have number between 1 to 10.

    i need to check that in 1 table

    i have following number

    1,2,3,4, 7,8

    so s5, 6, 9, 10 are missing in 1 table.

    how u will do in while or for loop,same thing i need to do for date

  • Okay, some may think I will have crossed a line, but here it goes, the best advice I can give you.

    Go to your boss and ask for help. You are obviously in over your head and you are expecting (no, more like demanding) that we solve your problem without any assistance from you in any way.

    Your boss is in a much better position to provide you the assistance you need because he (or she) has access to everything you do.

Viewing 15 posts - 16 through 30 (of 87 total)

You must be logged in to reply to this topic. Login to reply