Professional Web Applications Themes

Need a good query for calculating ulative totals - Microsoft SQL / MS SQL Server

vivek, It is not possible to get the result set you wanted with existing table structure.   There is no relation between starting value and value date as required in your output. You need to convert your two tables to one and use cross join to get the required result set. create table tbl ( My_Identifier char(1), ValueDate smalldatetime, Value money ) insert into tbl values('x','01 Apr 2003',1000) insert into tbl values('x','02 Apr 2003',-100) insert into tbl values('x','05 Apr 2003',530) insert into tbl values('x','17 Apr 2003',-600) select A.My_Identifier,A.ValueDate,sum(B.value) as RunningTotal from tbl A cross join tbl B where A.My_Identifier=B.My_Identifier and ...

  1. #1

    Default Re: Need a good query for calculating ulative totals

    vivek,

    It is not possible to get the result set you wanted with existing table
    structure.
     
    There is no relation between starting value and value date as required in
    your output.

    You need to convert your two tables to one and use cross join to get the
    required result set.


    create table tbl
    (
    My_Identifier char(1),
    ValueDate smalldatetime,
    Value money
    )
    insert into tbl values('x','01 Apr 2003',1000)
    insert into tbl values('x','02 Apr 2003',-100)
    insert into tbl values('x','05 Apr 2003',530)
    insert into tbl values('x','17 Apr 2003',-600)

    select A.My_Identifier,A.ValueDate,sum(B.value) as RunningTotal
    from tbl A cross join tbl B
    where A.My_Identifier=B.My_Identifier and A.ValueDate >= B.ValueDate
    group by A.My_Identifier,A.ValueDate
    order by A.My_Identifier,A.ValueDate

    Drop Table tbl


    Thanks,
    Praveen Maddali,
    MCSD, MCDBA





    "Vivek Dangwal" <com> wrote in message
    news:#phx.gbl...
    Hi everyone,

    I have a table which stores starting values (values as of the start date,
    let's say '1 April 2003').

    Let's call the table 'tbl_A'; I'll give a design of this table as below:

    Fields in tbl_A
    --------------------------
    My_Identifier (varchar)
    StartingValue (money)

    My other table contains records of transactions with the values.

    Fields in tbl_B
    --------------------------
    My_Identifier (varchar)
    ValueDate (datetime)
    Value (money)


    What I require is a field showing the running total of the value. I think an
    example will make it clear:

    Records in tbl_A are as follows:

    My_Identifier StartingValue
    ------------- -------------
    X +1000.00

    Records in tbl_B are as follows:

    My_Identifier ValueDate Value
    ------------- --------- -----
    X 02 Apr 2003 -100.00
    X 05 Apr 2003 +530.00
    X 17 Apr 2003 -600.00

    The desired result set would contain the following fields:

    My_Identifier ValueDate RunningTotal
    ------------- --------- ------------
    X 01 Apr 2003 +1000.00
    X 02 Apr 2003 +900.00
    X 05 Apr 2003 +1430.00
    X 17 Apr 2003 +830.00

    I hope you get the idea as to what I need.... somebody please help !!!


    praveen Guest

  2. #2

    Default Re: Need a good query for calculating ulative totals

    tbl_A in your schema is missing the required date (2003-04-01) but since
    that table is really not needed I'll ignore it:

    CREATE TABLE Sometable (valuedate DATETIME PRIMARY KEY, value NUMERIC(6,2))

    INSERT INTO Sometable VALUES ('20030401',1000)
    INSERT INTO Sometable VALUES ('20030402',-100)
    INSERT INTO Sometable VALUES ('20030405',+530)
    INSERT INTO Sometable VALUES ('20030417',-600)

    SELECT valuedate,
    (SELECT SUM(value)
    FROM Sometable AS S
    WHERE valuedate <= Sometable.valuedate)
    AS runningtotal
    FROM Sometable
    ORDER BY valuedate

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: Need a good query for calculating ulative totals

    The short answer is a self-join, something like:

    SELECT transaction_code, transaction_date,
    (SELECT SUM(amount)
    FROM BankAccount AS B1
    WHERE B1.transaction_date <= B2.transaction_date) AS balance
    FROM BankAccount AS B2;

    The right answer is that SQL is a datra retrieval language, not a report
    writer and you should be doing this with a report writer in the front
    end program. This self-join gets pretty slow when the volume increases.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Calculate ulative
    By premgrps@gmail.com in forum MySQL
    Replies: 1
    Last Post: June 29th, 02:58 PM
  2. Report - Query & Display help - How to get totals?
    By JethroTull in forum Macromedia ColdFusion
    Replies: 7
    Last Post: July 8th, 03:18 PM
  3. ASP totals and sub-totals from an Access or SQL DB
    By Laphan in forum ASP Database
    Replies: 3
    Last Post: April 2nd, 08:15 AM
  4. Putting query to good use.
    By nathan in forum ASP Database
    Replies: 7
    Last Post: October 7th, 12:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139