Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slim
Starting Member
3 Posts |
 Posted - 2004-08-17 : 11:20:22
|
| Hi,Here's the table I'm working with:CREATE TABLE [dbo].[Fund_Holdings] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FundCode] [varchar] (6) NULL , [TranDesc] [varchar] (20) NULL , [ContractNo] [varchar] (15) NULL , [PolicyNo] [varchar] (15) NULL , [UnitFlag] [char] (1) NULL , [TransUnits] [decimal](20, 2) NULL , [InitialUnitPrice] [decimal](12, 2) NULL , [AccUnitPrice] [decimal](12, 2) NULL , [TotalUnits] [decimal](20, 2) NULL , [UnitMovement] [char] (1) NULL , [TransDate] [datetime] NULL , [timestamp] [int] NULL , [CumulTotal] [decimal](12, 2) NULL )I am trying to populate the CumulTotal column with a running total of TotalUnits at the time of each transaction (TransDate). I want the CumulTotals per ContractNo per FundCode. I would like to see the following kind of thing:SELECT ContractNo, FundCode, TransDate, TotalUnits,CumulTotalFROM Fund_Holdings---------------------------------------------------ContractNo FundCode TransDate TotalUnits CumulTotalA123 XXZ 2003-12-01 12 12 A123 XXZ 2003-12-02 4 16A123 XXZ 2003-12-05 11 27A123 XXZ 2003-12-06 -12 15A123 YYA 2003-12-10 12 12A123 YYA 2003-12-11 2 14A123 YYA 2003-12-12 -3 11I can acheive this via cursors however with 2M rows to update it will take forever...If anyone has any ideas it would be much appreciated... |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-17 : 11:43:21
|
| i'm guessing something like this:SELECT ContractNo, FundCode, TransDate, TotalUnits, COALESCE((SELECT SUM(TotalUnits) FROM Fund_Holdings t2 WHERE t2.TransDate < t1.ITransDate),0) AS CumulTotalFROM Fund_Holdings t1order by TransDateGo with the flow & have fun! Else fight the flow :) |
 |
|
|
|
|
|
|
|
Calculating Cumulative Totals