SQL Server Performance

Query

Discussion in 'T-SQL Performance Tuning for Developers' started by Hamid, May 22, 2007.

  1. Hamid New Member

    hi.i wanna write a query to receive the following result in sql server2000.

    Code ****er bd_bs price remainder
    ----- ------ ----- ------ ---------
    1 1 0 10000 10000
    1 2 1 4000 6000
    1 3 0 1500 7500
    1 4 0 1000 8500
    1 5 1 8500 0

    my table contain fields (code,counter,bd_bs,price)
    i wanna receive column remainder in result.
    thanks

  2. khtan New Member


    declare @tbl table
    (
    Code int,
    Counter int,
    bd_bs int,
    price int
    )

    insert into @tbl
    select1, 1, 0, 10000union all
    select1, 2, 1, 4000union all
    select1, 3, 0, 1500union all
    select1, 4, 0, 1000union all
    select1, 5, 1, 8500

    select*,
    remainder= (select sum(case when x.bd_bs = 0 then price else -price end)
    from @tbl x where x.Code = t.Code and x.Counter <= t.Counter)
    from@tbl t
    order by Code, Counter



    KH
  3. Madhivanan Moderator

    If you want to show data in reports, make use of its Running Total feature

    Madhivanan

    Failing to plan is Planning to fail
  4. Hamid New Member

    Thanks.But i have a bit problem with this query.
    when i writing this query with this data.
    // Query/////////////////////
    selectsanad,rdf,kol_n,moen_n,tafzil_n,disk1,date1,price,bd_bs,remainder= (select sum
    (case when x.bd_bs = 1 then price else -price end)
    from f_sanad x where x.sanad = t.sanad and x.rdf <= t.rdf)fromf_sanad t
    where kol_N=10
    order by sanad, rdf
    // Query----------------/////////////////////

    Data-////////////////////////////////////////
    Sanad rdf date price bd_bs remainder
    -------------------------------------------------------
    754181/03/01100000011000000
    931281/03/06100000000
    1560181/03/18200000012000000
    1561281/03/18200000000
    1753181/03/25200000012000000
    1754281/03/25100000011000000
    1755281/03/25200000000
    1755481/03/25100000000
    1799181/03/25100000011000000
    1800181/03/25200000012000000
    1801181/03/25200000012000000
    1802181/03/25100000011000000
    1803181/03/25200000012000000
    1804681/03/25800000000
    1827181/03/27100000011000000
    1828281/03/27100000000
    2191181/04/08100000011000000
    2192281/04/08100000000
    2524181/04/17200000012000000
    2525281/04/17200000000

    ----------------------------------------------------
    now in column remainder when 2 bd_Bs rows is 1 sum is incorrect.

    Sanad rdf date price bd_bs remainder(incorrect) remainder(Correct)
    -----------------------------------------------------------------------------------------
    1799181/03/25100000011000000 1000000
    1800181/03/25200000012000000 3000000
    1801181/03/25200000012000000 5000000
    1802181/03/25100000011000000 6000000
    1803181/03/25200000012000000 8000000
  5. Hamid New Member

    Can anything help me?
  6. FrankKalis Moderator

    Not sure if that is what you want, but here it goes:


    select*,
    (SELECT SUM(price)
    FROM @tbl x
    WHERE x.Code = t.Code
    AND x.Counter <= t.Counter) AS remainder
    from@tbl t
    order by Code, Counter


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  7. FrankKalis Moderator

  8. Hamid New Member

    Please Test your query with this data:
    compound of Code And counter is Pk. When code is 1 and repeat then counter auto incremenet.
    when code is 2 counter is 0.
    when i want to recieve one report and use where clause in query, some rows deleted.
    for example the following data.
    but in the normal qualification each code at least have 2 rows that counter is 0 and 1.
    Code ,Counter,bd_bs,price,Remainder
    ---- ------- ----- ----- ---------
    1 , 0 , 0 , 1000 ,1000
    1 , 1 , 1 , 2000 , -1000
    1 , 2 , 1 , 1000 , -2000
    2 , 1 , 1 , 3500 , -5500
    3 , 1 , 1 , 2500 , -8000
    4 , 0 , 0 , 4300 , -3700
    4 , 1 , 0 , 2000 , -1700
  9. Hamid New Member

    I need help
  10. Adriaan New Member

    Do you want to have a running total for the Remainder? Please do this in a reporting tool - although it is possible to create this kind of functionality in SQL Server, there are tools out there that are designed to do this for you.
  11. Hamid New Member

    Thanks.when Number of code increment, Data remainder is new data instead of sum all remainder data.

Share This Page