Updating an "aggregated" table

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

SQL Server Development (2000)

Updating an "aggregated" table

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 07:47:54

I my db I have basically two indentical tables where one holds aggregates from the other table. They look like this:

DECLARE @table table
  (UserID int, MarketID int, Price int, Volume int, Active tinyint)
DECLARE @aggregate table
  (UserID int, MarketID int, Price int, Volume int)

A user can have several rows in @table with the same market and price but different volume so the volume is then aggregated and put in to @aggregate so that each user only has one row per price in a market (this is done for rapid reading purposes). So far everything works great and @aggregate is filled with data from a trigger.

However, when a user deactivates all records for a price in @table, the corresponding row in @aggregate should be deleted because there is no data to support that price beeing there anymore. And the trigger naturally only aggregate the active prices omitting those that are not active instead of suming it to 0. @aggregate is read using WITH (NOLOCK) so I'm afraid deleting all rows for a market and inserting only the active ones again will cause empty markets on my website...does anybody have any tricks up their sleeve to work this out?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-10 : 07:55:47

How about updating the volume in the aggregate table to 0 when a user is deactivated and selecting only rows from aggregate where volume > 0

Edit: or better still only selecting active users info

Duane.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 08:28:13

I guess I could do that, but how would I do a SUM(Volume) aggregate grouped by UserID, MarketID, Price on the following data when volume should be regarded as 0 when Active = 0:

UserID   MarketID   Price   Volume   Active
-------------------------------------------
1        1          15      100      1
1        1          16      250      1
1        1          15       62      1
1        1          16      912      1
1        1          15      401      0
1        1          16       52      0

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 08:31:42

Hm, I guess a SUM(Volume = CASE WHEN Active = 1 THEN Volume ELSE 0 END) would do the trick...let me try and see if it works out.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-10 : 08:38:42

close...try: Volume = SUM(CASE WHEN Active = 1 THEN Volume ELSE 0 END)

Corey

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-11 : 12:03:10

I did like ditch said and updated with 0 istead of deleting the row. There was an issue with how to decide if I needed to do an insert or an update but I solved that by making sure that all prices regardless of volume or active status were in the aggregated table before I actually did the update. Works exactly the way I want it to now except for the table spool/eager spool that's haunting me a bit...feel free to contribute there also

-> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38480

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources