updating by default/specific values

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

General SQL Server Forums

Database Design and Application Architecture

updating by default/specific values

Author

Topic

ChrisSamsDad
Starting Member

2 Posts

Posted - 2007-04-05 : 06:27:59

In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.

I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.

E.G.

AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69

To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.

UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).

This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.

However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.

Has anyone come across a situation like this (and found a neater solution).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 09:14:18

UPDATE facttable SET ft.revenue = COALESCE(rev1.revenue, rev2.revenue, 0)
FROM facttable as FT
LEFT JOIN revenues as rev1 ON rev1.AffilateID = FT.AffiliateID and rev1.TypeID = FT.TypeID
LEFT JOIN revenues as rev2 ON rev2.AffilateID = 0 and rev2.TypeID = FT.TypeID

Peter Larsson
Helsingborg, Sweden

ChrisSamsDad
Starting Member

2 Posts

Posted - 2007-04-05 : 09:17:09

quote:
Originally posted by Peso

UPDATE facttable SET ft.revenue = COALESCE(rev1.revenue, rev2.revenue, 0)
FROM facttable as FT
LEFT JOIN revenues as rev1 ON rev1.AffilateID = FT.AffiliateID and rev1.TypeID = FT.TypeID
LEFT JOIN revenues as rev2 ON rev2.AffilateID = 0 and rev2.TypeID = FT.TypeID

Peter Larsson
Helsingborg, Sweden

Yes, coalesce is probably better there, and thanks for correcting the join. Still got the problem though folks, anyone?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources