SQL Server Performance

Query Manipulation

Discussion in 'General Developer Questions' started by Jeya prabhu, May 31, 2006.

  1. Jeya prabhu New Member

    Hello
    In my table there is only one column(numeric) containing the value

    value
    --------------------
    1
    10
    11
    50
    63
    72
    0
    2
    10
    5
    100

    Now I want the out put in the format of.....
    1+10
    11+50
    63+72
    0+2
    10+5
    100--because toatal values are odd so it displays the final one without adding it

  2. Jeya prabhu New Member

    ALTER PROCEDURE pr_test1
    as
    BEGIN

    declare @prev numeric
    declare @curr numeric
    declare @tot numeric
    declare @value numeric
    declare @i int
    declare @cnt int
    set @i = 1
    declare @test_cursor CURSOR
    set @test_cursor = CURSOR FAST_FORWARD
    FOR select value from test1
    create table #temp_test(value numeric)

    set @cnt = (select count(*) from test1)
    OPEN @test_cursor
    FETCH NEXT FROM @test_cursor INTO @value
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    if @i = 1
    begin
    set @prev = @value
    set @i = 2
    end
    else
    begin
    set @curr = @value
    set @tot = @prev + @curr
    insert into #temp_test values(@tot)
    set @i = 1
    end
    END
    FETCH NEXT FROM @test_cursor INTO @value
    END
    print @cnt % 2 -- if the total number of records are odd, then to print the last record.
    if @cnt % 2 <> 0
    begin
    insert into #temp_test values(@value)
    end
    select value from #temp_test
    END
    CLOSE @test_cursor
    DEALLOCATE @test_cursor

    ------------------------
    By using this I got the result, but I want the result without using the temp table....by using the select query


    S.Jeya Prabhu

    "A ship in Harbour is safe,
    But that is not what ships are built for."
  3. Adriaan New Member

    As long as we don't know the answer to one simple question, there is no way that we can give any structural suggestions. That question is: "Why are the values in this particular order?"

    If your source data has row numbers 1, 2, 3 ... 11, then a query like this will give you the desired results:

    SELECT
    CAST(even_row.value AS VARCHAR(10))
    + CASE WHEN odd_row.value IS NOT NULL THEN '+' + CAST(odd_row.value AS VARCHAR(10)) ELSE '' END
    FROM
    (SELECT row_number, value FROM table WHERE row_number % 2 = 0) even_row
    LEFT JOIN
    (SELECT row_number, value FROM table WHERE row_number % 2 = 1) odd_row
    ON even_row.row_number = (odd_row.row_number - 1)

    If you don't know whether there is a gap in the row numbering, you'll need to copy all data ordered by the row_num into a temp table with an identity column, then use that identity column as the row number for the JOIN.

    Of course in SQL 2005 you will have a function that gives you a row number within a rowset, which allows you to avoid the temp table.

    [Edit: ON even_row.row_number = (even_row.row_number - 1) was incorrect ...]
  4. Jeya prabhu New Member

    first of all I Thank u "Adriaan"

    This is for our client need....Instead of adding(which I specified there) we have to find the varience and co-varience, but before calculating that...I just check this by add...but one thing I specfied wrongly...that calculating two, two records...but it should come like fibanacci serious...
  5. Adriaan New Member

    What's the defitinion of the Fibonacci series - each number in the series is the sum of all previous numbers?

    If you have the order of the rows worked out, you could try a query like this - but beware: it will take exponentially longer as the number of rows increases ...

    SELECT t.value + (SELECT SUM(r.value) FROM table r WHERE r.row_number < t.row_number)
    FROM table t
    ORDER BY t.row_number

    You will need an index on row_number!

    Because of the exponentially growing execution time, I would definitely check whether a cursor-based procedure is perhaps faster.
  6. FrankKalis Moderator

    quote:
    What's the defitinion of the Fibonacci series - each number in the series is the sum of all previous numbers?
    Not correct. Each number in the series is "only" the sum of its two predecessors.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  7. Jeya prabhu New Member

    Fibanacci series is 1+2,2+3,3+4,4+5,5+6............

    S.Jeya Prabhu

    "A ship in Harbour is safe,
    But that is not what ships are built for."
  8. Adriaan New Member

    quote:Originally posted by FrankKalis


    quote:
    What's the defitinion of the Fibonacci series - each number in the series is the sum of all previous numbers?
    Not correct. Each number in the series is "only" the sum of its two predecessors.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
    OK then -

    SELECT a.value,
    (SELECT SUM(x.value)
    FROM (SELECT TOP 2 b.value FROM tbl b WHERE b.row_number < a.row_number ORDER BY b.row_number DESC) X)
    FROM tbl a
    ORDER BY a.row_number
  9. Jeya prabhu New Member

    sorry adriaan think confused u...

    I want
    1+10 ------> as 11
    11+50------> as 61
    63+72------> as 135
    0+2--------> as 2
    10+5-------> as 15
    100--because total values are odd so it displays the final one without adding it

    not in the given format....and also I tried out this below query

    SELECT
    even_row.value
    + CASE WHEN odd_row.value IS NOT NULL THEN + odd_row.value ELSE '' END
    FROM
    (SELECT row_number, value FROM test1 WHERE row_number % 2 = 1) odd_row
    LEFT JOIN
    (SELECT row_number, value FROM test1 WHERE row_number % 2 = 0) even_row
    ON even_row.row_number = (odd_row.row_number - 1)
    ----------------
    Result
    ======
    ---------------------
    NULL
    5
    9
    13
    17
    21
    113
    72
    12
    105

    (10 row(s) affected)

    First value comes as the null value but the remaining things are fine
  10. Adriaan New Member

    You must have missed my last post before yours ... Here's one that starts at the third row, and calculates the sum of the two previous rows - note that you do need a row_number column, or the only alternative is a cursor, which probably takes more time to process:

    SELECT
    (SELECT SUM(x.value) FROM (SELECT TOP 2 b.value FROM tbl b WHERE b.row_number < a.row_number ORDER BY b.row_number DESC) X)
    FROM tbl a
    WHERE (SELECT COUNT(*) FROM tbl c WHERE c.row_number < a.row_number) > 1
    ORDER BY a.row_number
  11. Jeya prabhu New Member

    Thank u very much this is the result I want...

    S.Jeya Prabhu

    "A ship in Harbour is safe,
    But that is not what ships are built for."
  12. Madhivanan Moderator

    If Adriaan's code gives you the correct result then , you can alos use this


    Select t1.value+t2.value from tbl t1
    inner join tbl t2 on t1.row_number=t2.row_number-1


    Madhivanan

    Failing to plan is Planning to fail
  13. Adriaan New Member

    Madhivanan, that's too simple, given the problem. You need to be certain of the order of the rows, and you need to skip the first two rows. Also, your query depends on a list of row_number values without any gaps.
  14. Madhivanan Moderator

    Well. In that case,


    Declare @rows int
    Select @rows=max(row_number) from tbl
    select @rows

    If @rows%2=0
    Select t1.value+t2.value from tbl t1
    inner join tbl t2 on t1.row_number=t2.row_number-1

    else
    Select t1.value+t2.value from tbl t1
    inner join tbl t2 on t1.row_number=t2.row_number-1
    and t2.row_number<@rows


    Madhivanan

    Failing to plan is Planning to fail
  15. Jeya prabhu New Member

    Adriaan query is ok but it won't calculate the values for the last record...and madhivanan's is calculating that too....

    Once again Thank u All.
  16. Madhivanan Moderator

    quote:Originally posted by Jeya prabhu

    Adriaan query is ok but it won't calculate the values for the last record...and madhivanan's is calculating that too....

    Once again Thank u All.
    So which one do you want to use now?

    Madhivanan

    Failing to plan is Planning to fail
  17. cmdr_skywalker New Member

    "...we have to find the varience and co-varience.."

    Use the SQL VAR (sample variance) or VARP (population variance) functions.
    I am not sure if you're computing for the coefficient of variation (standard deviation/Population Mean) or the covariance of X and Y = Mean Value of (X - Population Mean X)(Y - Population Mean Y)


    May the Almighty God bless us all!
    www.empoweredinformation.com
  18. FrankKalis Moderator

  19. Jeya prabhu New Member


    This one

    Select t1.value+t2.value from tbl t1
    inner join tbl t2 on t1.row_number=t2.row_number-1

  20. Jeya prabhu New Member


    So which one do you want to use now?

    Madhivanan

    my past reply is for Madhivana

    S.Jeya Prabhu

    "A ship in Harbour is safe,
    But that is not what ships are built for."
  21. Madhivanan Moderator

    Well. What is your answer for Frank's question?

    Madhivanan

    Failing to plan is Planning to fail
  22. Jeya prabhu New Member

    To Frank
    This calculation is to find the BSE (Bombay Stack Exchange)...for that we have to find varience instead of adding and between range of dates...So first I tried will the simple things...Then I will go for that...

    Thank U
  23. FrankKalis Moderator

    I suspected something like that. As long as you need simple things like variance it should be fine to do this with SQL Server. However, more sophisticated measures aren't really fun on larger tables and you might in the end be better off just returning the resultset to the client and do the calculations there. SQL isn't really made for complex calculations.

    Btw, Steve Kass has several interesting statistical scripts on his homepage:http://www.users.drew.edu/skass/sql/

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page