how can i insert in udf

  • SELECT SUM(I1) FROM TABLE_THAT_HAS_I1_AS_A_COLUMN

  • hbtkp (4/16/2012)


    i dont know what is cumulative formula.

    but this is how i am getting my final data in report.

    so based on how they calculate , i need to do implement that formula in sql.

    this result is accurate.i need to figure out formula based on that

    Well how do you expect somebody else to help with a formula when you can't explain how to calculate it?

    Let me try to paraphrase in extremely clear english what your questions is.

    The chemical billfold yields to a crazy accent. Behind the fan waits my dress. The infant mortal pilots an ace near a stretch wash. A farewell word sneaks with the exit.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • hbtkp (4/16/2012)


    i dont know what is cumulative formula.

    but this is how i am getting my final data in report.

    so based on how they calculate , i need to do implement that formula in sql.

    this result is accurate.i need to figure out formula based on that

    Then the first thing you need to do is find out from you colleagues how that is calculated (because we sure as hell don't know) and work from there.

    Whatever it is, it's not a standard calculation and there is no way we can guess what your colleagues may have done.

    For the nth time, speak to your boss/manager/colleagues and ask them for help.

    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/16/2012)


    i dont know what is cumulative formula.

    but this is how i am getting my final data in report.

    so based on how they calculate , i need to do implement that formula in sql.

    this result is accurate.i need to figure out formula based on that

    Here is the values you originally posted.

    NUMBER CUMULATIVE

    0.00

    2.78 2.78

    1.01 3.82

    2.07 5.97

    1.71 7.78

    2.21 10.16

    0.23 10.41

    0.69 11.17

    -0.01 11.16

    -1.70 9.27

    5.51 15.29

    -2.47 12.44

    -4.28 7.63

    2.97 10.82

    -2.50 8.06

    2.38 10.62

    You are telling me you can't tell me how to calculate the value in the second column based on the first value and the values that precede the row? If you can't tell us that, how do you expect us to tell you how to code it?

    Please get real. You have got to start helping us to understand what it is you are trying to accomplish. We can not read your mind, nor can we see what you are doing, nor can we access your systems to figure out what you need to do.

    Answer us this, have you even bothered to read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/?

    If not, why not? If so, why haven't you bothered to provide us with what we have been asking you to provide?

  • this is what i have to figure out ,how they calculate it.

  • Well figure that out and get back to us. We can help you recreate a formula in sql. We will not be able to help you figure out what that formula is. That formula is a business rule not a sql thing. We are experts at SQL not your business.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • hbtkp (4/16/2012)


    this is what i have to figure out ,how they calculate it.

    Ask the people you work with. If they can't tell you, you are straight out of luck. Sorry.

  • How about

    SELECT *

    FROM

    (

    VALUES

    ( 0, NULL ),

    ( 2.78, 2.78 ),

    ( 1.01, 3.82 ),

    ( 2.07, 5.97 ),

    ( 1.71, 7.78 ),

    ( 2.21, 10.16 ),

    ( 0.23, 10.41 ),

    ( 0.69, 11.17 ),

    ( -0.01, 11.16 ),

    ( -1.7, 9.27 ),

    ( 5.51, 15.29 ),

    ( -2.47, 12.44 ),

    ( -4.28, 7.63 ),

    ( 2.97, 10.82 ),

    ( -2.5, 8.06 ),

    ( 2.38, 10.62 )

    ) AS UnexplicableCumulative (item2, item3)

    That should do the trick.

    Sorry, couldn't help it 😀

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • hbtkp (4/13/2012)


    i dont need to change field, i have filed

    CREATE FUNCTION item1(@Data [varbinary](max))

    RETURNS TABLE (

    item2 varchar,

    item3 int)

    now i need to add more rows in item3, and i need to implement in another sp.

    I especially like the varbinary(max) parameter. What is it you are passing in there? The contents of an entire FaceBook NoSQL database?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken (4/17/2012)


    How about

    SELECT *

    FROM

    (

    VALUES

    ( 0, NULL ),

    ( 2.78, 2.78 ),

    ( 1.01, 3.82 ),

    ( 2.07, 5.97 ),

    ( 1.71, 7.78 ),

    ( 2.21, 10.16 ),

    ( 0.23, 10.41 ),

    ( 0.69, 11.17 ),

    ( -0.01, 11.16 ),

    ( -1.7, 9.27 ),

    ( 5.51, 15.29 ),

    ( -2.47, 12.44 ),

    ( -4.28, 7.63 ),

    ( 2.97, 10.82 ),

    ( -2.5, 8.06 ),

    ( 2.38, 10.62 )

    ) AS UnexplicableCumulative (item2, item3)

    That should do the trick.

    Sorry, couldn't help it 😀

    Personally, I attribute the values in the second column to poor math skills. If that column is correct, I would like to see the formula used in the calculation because I can't decern a pattern.

  • Looks like OP need just a running total.

    My be sample provide is not very exact 🙂

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (4/17/2012)


    Looks like OP need just a running total.

    My be sample provide is not very exact 🙂

    Already suggested that and was told no, it wasn't a running total. Still waiting for the OP to explain what it is.

  • hbtkp (4/16/2012)


    it snot running total,if you see it carefully,

    its not just adding values. sometimes its adding 1 ,sometimes it minus 1

    Can you tell please which rows of provided samples do you refer to? Where it does add 1 and wher it does -1?

    If you do the following:

    SELECT IDENTITY(int,1,1) as RN,*

    into #t

    FROM

    (

    VALUES

    ( 0, NULL ),

    ( 2.78, 2.78 ),

    ( 1.01, 3.82 ),

    ( 2.07, 5.97 ),

    ( 1.71, 7.78 ),

    ( 2.21, 10.16 ),

    ( 0.23, 10.41 ),

    ( 0.69, 11.17 ),

    ( -0.01, 11.16 ),

    ( -1.7, 9.27 ),

    ( 5.51, 15.29 ),

    ( -2.47, 12.44 ),

    ( -4.28, 7.63 ),

    ( 2.97, 10.82 ),

    ( -2.5, 8.06 ),

    ( 2.38, 10.62 )

    ) AS UnexplicableCumulative (item2, item3)

    alter table #t add rt decimal(10,2)

    create unique clustered index ix_#t on #t(rn)

    declare @rt decimal(10,2)

    set @rt= 0

    update #t

    set @rt = rt = @rt+Item2

    from #t

    option (maxdop 1)

    select *, rt-item3 as [Diff to running total] from #t

    You can see:

    RNitem2item3rtDiff to running total

    10.00NULL0.00NULL

    22.782.782.780.00

    31.013.823.79-0.03

    42.075.975.86-0.11

    51.717.787.57-0.21

    62.2110.169.78-0.38

    70.2310.4110.01-0.40

    80.6911.1710.70-0.47

    9-0.0111.1610.69-0.47

    10-1.709.278.99-0.28

    115.5115.2914.50-0.79

    12-2.4712.4412.03-0.41

    13-4.287.637.750.12

    142.9710.8210.72-0.10

    15-2.508.068.220.16

    162.3810.6210.60-0.02

    The fluctuations in bold, means:

    1. your sample is not exact as should be

    or

    2. there are another data involved in calculation

    or

    3. Your formula is based on complex differential equation

    If your case is No 1 then you just need a running total

    If your case is No 2 then you need to find what other data involved

    If your case is No 3 then you need to find what formula is used or consult top mathematician/quant engineer to build one, but he will ask you for much larger sample and it will cost, most likely, your annual salary without guaranteed solution

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • ok. i already posted my formula for cumulative.

    i hv another prov

    i have delcare variable

    declare @abd datetime

    now i need to get 1 date from function

    like

    select @abd = fromdate from item2(@rdfd)

    fromdate hace same value in all filed

    so i just want 1 value

    can i use like this

    select @abd =distinct fromdate from item2(@rdfd)

    i cannot use where ,this is udf

  • hbtkp (4/17/2012)


    ok. i already posted my formula for cumulative.

    i hv another prov

    i have delcare variable

    declare @abd datetime

    now i need to get 1 date from function

    like

    select @abd = fromdate from item2(@rdfd)

    fromdate hace same value in all filed

    so i just want 1 value

    can i use like this

    select @abd =distinct fromdate from item2(@rdfd)

    i cannot use where ,this is udf

    You didn't provide any formula in this post! Here is your words:

    i dont know what is cumulative formula.

    but this is how i am getting my final data in report.

    so based on how they calculate , i need to do implement that formula in sql.

    this result is accurate.i need to figure out formula based on that

    And for your another problem:

    Selecting value into variable will guarantee that you will have only one of it regardless of using WHERE or DISTINCT as single non-table type variable may only hold single value.

    The problem is you never know which value is taken from range, but if in your case the function returns list of dates and all of them the same, you shouldn't care: the following will work just fine:

    select @abd = fromdate from item2(@rdfd)

    BTW, if your UDF is table-valued and it is table valued as you select from it, you can use WHERE!

    I've seen you claiming to know the SQL basics... I'm afraid you're wrong mate.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 15 posts - 46 through 60 (of 85 total)

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