Sum It to this day - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sum It to this day

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-07-28 : 20:42:01

Good day!

i had this sample data...

SELECT '1605-00289' as ID, 'John Doe' as Name, '2005-04-04 00:00:00.000' as dtr_date, '1.50' as credited
union all
SELECT '1605-00289', 'John Doe', '2005-04-05 00:00:00.000', '1.50'
union all
SELECT '1605-00289', 'John Doe', '2005-04-06 00:00:00.000', '1.50'
union all
SELECT '1605-00289', 'John Doe', '2005-04-07 00:00:00.000', '1.50'
union all
SELECT '1605-00289', 'John Doe', '2005-04-08 00:00:00.000', '2.00'
union all
SELECT '1605-00289', 'John Doe', '2005-04-09 00:00:00.000', null

I want to display this query by taking the sum of "credited" by ID and be credited to date "2005-04-09"

RESULT IS...

ID          Name      dtr_date                 Credited
-------------------------------------------------------

1605-00289  John Doe  2005-04-04 00:00:00.000  1.50
1605-00289  John Doe  2005-04-05 00:00:00.000  1.50
1605-00289  John Doe  2005-04-06 00:00:00.000  1.50
1605-00289  John Doe  2005-04-07 00:00:00.000  1.50
1605-00289  John Doe  2005-04-08 00:00:00.000  2.00
1605-00289  John Doe  2005-04-09 00:00:00.000  8.00

How?

Want Philippines to become 1st World COuntry? Go for World War 3...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 20:53:38

[code]UPDATE testTbl
SET CREDITED = (SELECT SUM(Credited) FROM testTbl WHERE ID = T.ID AND dtr_date < T.dtr_date)
FROM testTbl T
WHERE Credited IS NULL[/code]
Does this do what you want?

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-07-28 : 20:58:45

Thnks timmy...

I don't want to do the update first. i want to display it.

Want Philippines to become 1st World COuntry? Go for World War 3...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 21:06:55

Easy:

SELECT ID, Name, dtr_date, ISNULL(credited, (SELECT SUM(Credited) FROM testTbl WHERE ID = T.ID AND dtr_date < T.dtr_date)) as Credited
FROM testTbl T

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-07-28 : 21:10:59

:D

your query will not be apply if the record has different ID's with different "Credited".

Want Philippines to become 1st World COuntry? Go for World War 3...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 21:43:49

I thought that was the idea. But you have the basic gist now and should be able to customise it for your needs.

Tim

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-29 : 00:33:00

explore group by then use cube or rollup to get running total (see bol for more details)

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources