Post reply

T-SQL question

  • November 19, 2004 at 1:32 pm

    #165459

    I am a beginner in SQL sp's. I have a 'simple' question.

    The table:

    id        ordr    vin    vout

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

    1        2        8        6

    2        4        3        2

    3        1        9        1

    4        7        11       5

    5        6        4        4

    6        3        8        2

    7        5        3        6

    Query:

    < select id, ordr, vin, vout, ?vin-vout as sldo? from table order by ordr >

    the part between ? is that I dont know.

    this is the result I want to:

    id      ordr     vin     vout    sldo

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

    3        1        9        1        8         -- 9 - 1

    1        2        8        6        10        -- (8) + 8 - 6 -> (8) result from previous row

    6        3        8        2        16        -- (10) + 8 - 2 -> (10) result from previous row, etc..

    2        4        3        2        17        -- (16) + 3 - 2

    7        5        3        6        14        -- (17) + 3 - 6

    5        6        4        4        14        -- (14) + 4 - 4

    4        7        11      5        20        -- (14) - 11 + 5

    Thanks a lot for your help.

  • November 19, 2004 at 1:49 pm

    #530905

    there are # of ways to do this. This is one of them :

    how about : (#tmp1 is your table)

    create table #tmp1 (id int identity(1,1) not null, ordr int not null , vin int not null, vout int not null)

    go

    set nocount on

    insert into #tmp1 (ordr, vin, vout) values (2,        8,        6)

    insert into #tmp1 (ordr, vin, vout) values (4,        3,        2)

    insert into #tmp1 (ordr, vin, vout) values (1,        9,        1)

    insert into #tmp1 (ordr, vin, vout) values (7,        11,       5)

    insert into #tmp1 (ordr, vin, vout) values (6,        4,        4)

    insert into #tmp1 (ordr, vin, vout) values (3,        8,        2)

    insert into #tmp1 (ordr, vin, vout) values (5,        3,        6)

    set nocount off

    select id, ordr, vin, vout, vin-vout as sldo

    , (select sum(vin) - sum(vout) from #tmp1 where ordr <= T.ordr) as running_turnover

    from #tmp1 T

    order by ordr

    -- this way of using nested tableexpressions makes it harder to read when the query gets pritty large.

    -- You'll have to test it to see if it suits your goals performancewize.

    drop table #tmp1

    results :

    id          ordr        vin         vout        sldo        running_turnover

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

    3           1           9           1           8           8

    1           2           8           6           2           10

    6           3           8           2           6           16

    2           4           3           2           1           17

    7           5           3           6           -3          14

    5           6           4           4           0           14

    4           7           11          5           6           20

    (7 row(s) affected)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 19, 2004 at 4:17 pm

    #530925

    There of course ae plenty more ways to accomplish this as well it is really a matter of size of data going forward, potential for duplicate order numbers, and indexes.

    The suggested method works in most cases but when you get into larger sets of data can slow down.

    If your order number cannot be duplicate I would suggest using a temp table or table variable and load the orders in to it with details like so

    CREATE TABLE #Rpt (

     Ordr int Primary Key not null,

     vin int not null,

     vout int not null,

     running int null

    )

    Load the data then create a while loop or cursor to start from the first order and work thru all. You will also need an int variable to capture each rows final value to pass to the next rows data for use.

    Then select your output from the temp table as the final step.

    However even with that said, if you are using a reporting application such as crystal these can be done much simplier in the report or even a custom app. You should consider the tool first unless you are needing this in a bland output such as QA.

    Furthermore if this is going to be run often I suggest o ahead and summize into a column on the table or another table when the data changes to get the best performance you can.

  • November 19, 2004 at 4:35 pm

    #530927

    One alternative:

    select 
x.id, 
min(x.ordr) ordr, 
min(x.vin) vin, 
min(x.vout) vout, 
sum(y.vin-y.vout) sldo
from 
yourtable x cross join 
yourtable y 
where 
x.ordr>=y.ordr
group by 
x.id
order by 
min(x.ordr)

    Can't remember of the top of my head how this will compare performancewise to the above suggestions, but I'm sure all 3 options suggested so far will vary in comparison to each other depending on the number of rows.

  • November 19, 2004 at 5:56 pm

    #530939

    I have already used #tmp table to get the result, but I was trying to get the result with simple T-SQL.

    thanks,

    stojce

  • November 20, 2004 at 11:01 am

    #530951

    hey stojce

    try out this query...think it works....but ordering is a pblm

    i will check it out and reply to u

    table name--->s_add

    select t1.rid,t1.ordr,t1.vin,t1.vout,(select sum(t2.vin-t2.vout) from s_add t2

       where t2.rid<=t1.rid)from s_add t1

    regards

    Rajiv.

  • November 20, 2004 at 11:14 am

    #530952

    hey stojce

    can u tell me why is there a id field? u already have a ordr field which i presume is a primary key.is it?

    tell me whether the ordr field is a primary key.?

    if its a primary key then i think the pblm could b simple....else a bit complex?

    Regards

    Rajiv.

  • November 20, 2004 at 5:20 pm

    #530965

    Hi,

    the ordr field actually is the smalldatetime field and it can hold the multiple records with same value. Yes I know that I can solve this problem with #tmp table (or cursor), but I was wondering if this can be done with simple T-SQL.

    thanks

    stojce

  • November 20, 2004 at 7:01 pm

    #530968

    I've found exactly what I need, but in a Oracle

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    Cumulative Aggregate Function Example

    The following is an example of cumulative amount_sold by customer ID by quarter in 1999:

    SELECT c.cust_id, t.calendar_quarter_desc,
TO_CHAR (SUM(amount_sold), '9,999,999,999') AS Q_SALES,
TO_CHAR(SUM(SUM(amount_sold)) OVER (PARTITION BY
c.cust_id ORDER BY c.cust_id, t.calendar_quarter_desc ROWS UNBOUNDED
PRECEDING), '9,999,999,999') AS CUM_SALES
FROM sales s, times t, customers c
WHERE
s.time_id=t.time_id AND
s.cust_id=c.cust_id AND
t.calendar_year=1999 AND
c.cust_id IN (6380, 6510)
GROUP BY c.cust_id, t.calendar_quarter_desc
ORDER BY c.cust_id, t.calendar_quarter_desc;
  CUST_ID CALENDA Q_SALES        CUM_SALES
--------- ------- -------------- --------------
     6380 1999-Q1         60,621         60,621
     6380 1999-Q2         68,213        128,834
     6380 1999-Q3         75,238        204,072
     6380 1999-Q4         57,412        261,484
     6510 1999-Q1         63,030         63,030
     6510 1999-Q2         74,622        137,652
     6510 1999-Q3         69,966        207,617
     6510 1999-Q4         63,366        270,983
    I hope that these functions will be available in sql-2005

Viewing 9 posts - 1 through 8 (of 8 total)

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