I am so close to the answer but I've been banging my head against this particular wall for over a week now.
Our vendor database has a table with 10 different buckets that I need to add together. Easy enough on the face of it, except now our BU has come up with the requirement that when certain circumstances are met, some of the buckets should be excluded. Right now, they're only excluding one bucket but they may want more buckets excluded or a different bucket excluded in the future. To make this properly "plug and play," I'm trying to make it data driven so we only have to update a table to get the process working in the future.
So we have this table (not all the columns in the vendor table, but I don't care about the others):
CREATE TABLE #OrigTable (MyID INT NOT NULL IDENTITY(1,1), OrderID INT NOT NULL, Stuff1 MONEY, Stuff2 MONEY, Stuff3 MONEY, Stuff4 MONEY, Stuff5 MONEY, Stuff6 MONEY, Stuff7 MONEY, Stuff8 MONEY, Stuff9 MONEY, Stuff10 MONEY, TotalStuff MONEY);
--TotalStuff is an actual aggregated column of all the "Stuff" columns in this table
Then I created a flag table to track which buckets need to be excluded (not included) from the total calculation:
CREATE TABLE #BucketExclude (OrderType CHAR(3), Buck1 BIT NOT NULL, Buck2 BIT NOT NULL,
Buck3 BIT NOT NULL, Buck4 BIT NOT NULL, Buck5 BIT NOT NULL, Buck6 BIT NOT NULL, Buck7 BIT NOT NULL,
Buck8 BIT NOT NULL, Buck9 BIT NOT NULL, Buck10 BIT NOT NULL);
INSERT INTO TABLE #BucketExclude (OrderType, Buck1, Buck2, Buck3, Buck4, Buck5, Buck6, Buck7,
Buck8, Buck9, Buck10)
I'm looking at this now and thinking my best solution is to run a piece of code that actual checks the bit flags and subtracts the individual Stuff buckets from the TotalStuff column if the flag is 1 (meaning true for my purposes). But where I'm running into the wall is how to do this without rebar or a loop. I want to make this set-based but I'm not sure how to go about it.
I don't want to have to write out every iteration of "CASE WHEN buck1 = 1 and buck2 = 0 and buck3 = 0 … and buck10 = 0 THEN TotalStuff - Stuff1 WHEN CASE WHEN buck1 = 1 and buck2 = 1 …. THEN TotalStuff - (Stuff1 + Stuff2) … END"
Right now my Google Fu is pointing me to how to pull column names from INFORMATION_SCHEMA based on a flag, but that's not feeling like the right solution. All the other problem/solution threads found are for more simplistic SUM() problems.
Any thoughts on functions or google terms I can use to find a solution for this issue? Or am I just going to have to bite the bullet and either hard code the CASE from hell or loop through using dynamic SQL for this one?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.