# Calculating totals based on a column flag

• Brandie Tarvin

SSC Guru

Points: 172760

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)VALUES ('MAM',0,1,0,0,0,0,0,0,0,0),('DOF',1,0,0,0,0,0,0,0,0,0),('CAW',0,1,0,1,0,1,0,0,0,0),('GIV',0,0,0,1,1,0,0,0,0,0);`

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.

• ScottPletcher

SSC Guru

Points: 98556

I'll assume that:

1) you have some way to get the OrderType from the OrderID since you'll need the Type to join to the Buck bits table as it's given

2) you want to zero-out the individual bucket amounts in the final query if their bit is on, i.e., if buck1 is on, Stuff1 is zeroed out, etc.

Then, just off the top of my head:

`SELECT     Stuff1Adjusted,    Stuff2Adjusted,    [...],    TotalStuff = Stuff1Adjusted + Stuff2Adjusted + [...]FROM #OrigTable OTINNER JOIN #BucketExclude BE ON ...CROSS APPLY (    SELECT         OT.Stuff1 * ABS(CAST(BE.Buck1 AS tinyint) - 1) AS Stuff1Adjusted,        OT.Stuff2 * ABS(CAST(BE.Buck2 AS tinyint) - 1) AS Stuff2Adjusted,        [...]) AS ca1`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• Brandie Tarvin

SSC Guru

Points: 172760

Assumption #1, I do have a join table that I'm working off of and forgot to include.

Assumption #2, I can't (and don't want to) zero out the individual buckets. I only need to subtract them from the Total for my final query.

I keep forgetting about CROSS APPLY. I don't know why... Thanks, Scott. I'll do some noodling with that and see what I can come up with.

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.

• Jeffrey Williams

SSC Guru

Points: 88652

Expanding on Scott's idea - since you want a total of the values:

`SELECT     [...],    ca1.TotalStuffFROM #OrigTable OTINNER JOIN #BucketExclude BE ON ...CROSS APPLY (     SELECT TotalStuff = sum(StuffValues)       FROM (VALUES (OT.Stuff1 * ABS(CAST(BE.Buck1 AS tinyint) - 1))                  , (OT.Stuff2 * ABS(CAST(BE.Buck2 AS tinyint) - 1))                  , [...]            ) AS st(StuffValues)) AS ca1`

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• ScottPletcher

SSC Guru

Points: 98556

`SELECT     OT.Stuff1,    OT.Stuff2,    [...],    TotalStuff = TotalStuff -         OT.Stuff1 * CAST(BE.Buck1 AS tinyint) -        OT.Stuff2 * CAST(BE.Buck2 AS tinyint) -        OT.Stuff3 * CAST(BE.Buck2 AS tinyint) -        [...]FROM #OrigTable OTINNER JOIN #BucketExclude BE ON ...`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• drew.allen

SSC Guru

Points: 76739

I think this is simpler.  I used a CROSS JOIN, because it wasn't clear how to link the two tables.

`SELECT *FROM #OrigTable AS otCROSS JOIN #BucketExclude AS beCROSS APPLY(	SELECT SUM(c.StuffNum) StuffAdjustment	FROM	(		VALUES			(ot.Stuff1, be.Buck1)		,	(ot.Stuff2, be.Buck2)		,	(ot.Stuff3, be.Buck3)		,	(ot.Stuff4, be.Buck4)		,	(ot.Stuff5, be.Buck5)		,	(ot.Stuff6, be.Buck6)		,	(ot.Stuff7, be.Buck7)		,	(ot.Stuff8, be.Buck8)		,	(ot.Stuff9, be.Buck9)		,	(ot.Stuff10, be.Buck10)	) c(StuffNum, ExcludeFl)	WHERE c.ExcludeFl = 1) c`

Drew

J. Drew Allen

• Brandie Tarvin

SSC Guru

Points: 172760

The ABS(CAST(BE.Buck1 AS tinyint) - 1) doesn't work for me. It is zeroing out the flag that tells me whether or not to include the bucket.

Here's what I've come up with (and seems to be working)… Of course, I'm not updating the original vendor table, just a staging table that duplicates it long enough for me to get my feed data.

`UPDATE OTSET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)FROM #OrigTable OTINNER JOIN #JoinTableIForgot JTON OT.OrderID = JT.OrderIDINNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderTypeCROSS APPLY (     SELECT SUM(StuffValues) AS AdjustStuff, BucketExclude       FROM (VALUES (ISNULL(OT.Stuff1,0.00), BE.Buck1)                  , (ISNULL(OT.Stuff2,0.00), BE.Buck2)                  , [...]            ) AS st(StuffValues,BucketExclude)	   WHERE BucketExclude = 1	   GROUP BY BucketExclude) AS ca1`

Thanks for all the help, folks. I appreciate it.

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.

• ScottPletcher

SSC Guru

Points: 98556

I posted a simplified query if you don't need to list/modify the individual values.  To just subtract out the excluded values, you don't really need the CROSS APPLY:

`UPDATE OTSET     TotalStuff = TotalStuff -         OT.Stuff1 * CAST(BE.Buck1 AS tinyint) -        OT.Stuff2 * CAST(BE.Buck2 AS tinyint) -        OT.Stuff3 * CAST(BE.Buck2 AS tinyint) -        [...]FROM #OrigTable OTINNER JOIN #BucketExclude BE ON ...`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• Brandie Tarvin

SSC Guru

Points: 172760

ScottPletcher wrote:

I posted a simplified query if you don't need to list/modify the individual values.  To just subtract out the excluded values, you don't really need the CROSS APPLY:

`UPDATE OTSET     TotalStuff = TotalStuff -         OT.Stuff1 * CAST(BE.Buck1 AS tinyint) -        OT.Stuff2 * CAST(BE.Buck2 AS tinyint) -        OT.Stuff3 * CAST(BE.Buck2 AS tinyint) -        [...]FROM #OrigTable OTINNER JOIN #BucketExclude BE ON ...`

Ahhh. I'll look at this. I like this better.

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.

• Jeffrey Williams

SSC Guru

Points: 88652

Is there a reason you prefer subtracting the excluded amounts - instead of  just adding up the amounts for your own total?  Is it possible that the TotalStuff column has a value that is not a summation of the Stuff1 - Stuff10 columns?

It also appears that you can have NULL values in the data - which is not something I would have expected.  I would expect that if no value was available it would be set to 0.00.

You don't need to CAST the bucket columns - since they should contain either a 0 or 1 (not null) value.  But - how you utilize those columns will determine what you need to do...

For example - if you do this: (ot.Stuff1 * be.Buck1) - then you are going to be adding up the excluded values.  If you do this: abs(ot.Stuff1 * be.Buck1 - 1)) - then you would be adding up the included values (0 - 1 = -1 and 1 - 1 = 0).  So - either add up the included values to get to a Total - or add up the excluded values and subtract from TotalStuff to get to a Total.

And finally - using an INNER JOIN to BucketExclude requires that every possible OrderType have a value for every bucket.  Using an outer apply/join only requires the specific OrderType to be included in the table.  You also don't need to filter anything out of the outer apply with this example:

` Select *      , TotalStuff = coalesce(ot.TotalStuff, 0.00) - coalesce(ca.ExcludedStuff, 0.00)   From #OrigTable ot   Left Join #BucketExclude be On be.OrderType = ot.OrderType  Outer Apply (Select ExcludedStuff = sum(st.ExcludeStuff)                From (Values (ot.Stuff1 * be.Buck1)                           , (ot.Stuff2 * be.Buck2)                           , (ot.Stuff3 * be.Buck3)                           , (ot.Stuff4 * be.Buck4)                           , (ot.Stuff5 * be.Buck5)                           , (ot.Stuff6 * be.Buck6)                           , (ot.Stuff7 * be.Buck7)                           , (ot.Stuff8 * be.Buck8)                           , (ot.Stuff9 * be.Buck9)                           , (ot.Stuff10 * be.Buck10)                     ) As st(ExcludeStuff)              )                         ca;`

The value returned will either be the excluded amount - or 0.00 or NULL if the OrderType is not defined in BucketExcluded.

Back to the original table - if the value in TotalStuff can be NULL and you have an OrderType that has excluded values for that row then you are going to get a negative number if there is a valid value in the Stuff{n} column that is being excluded.  Which is why I am surprised to see that there are NULL values in TotalStuff.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• drew.allen

SSC Guru

Points: 76739

Brandie Tarvin wrote:

The ABS(CAST(BE.Buck1 AS tinyint) - 1) doesn't work for me. It is zeroing out the flag that tells me whether or not to include the bucket.

Here's what I've come up with (and seems to be working)… Of course, I'm not updating the original vendor table, just a staging table that duplicates it long enough for me to get my feed data.

`UPDATE OTSET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)FROM #OrigTable OTINNER JOIN #JoinTableIForgot JTON OT.OrderID = JT.OrderIDINNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderTypeCROSS APPLY (     SELECT SUM(StuffValues) AS AdjustStuff, BucketExclude       FROM (VALUES (ISNULL(OT.Stuff1,0.00), BE.Buck1)                  , (ISNULL(OT.Stuff2,0.00), BE.Buck2)                  , [...]            ) AS st(StuffValues,BucketExclude)	   WHERE BucketExclude = 1	   GROUP BY BucketExclude) AS ca1`

Thanks for all the help, folks. I appreciate it.

Why are you doing the `ISNULL()` check?  The `SUM()` will eliminate NULL values automatically.  Are you trying to eliminate the warning?

Why are you doing the `ISNULL()` check in the `VALUES` clause (multiple places) instead of in the `SELECT` clause (one place)?

`UPDATE OTSET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)FROM #OrigTable OTINNER JOIN #JoinTableIForgot JTON OT.OrderID = JT.OrderIDINNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderTypeCROSS APPLY (     SELECT SUM(ISNULL(StuffValues, 0.00)) AS AdjustStuff, BucketExclude       FROM (VALUES (OT.Stuff1, BE.Buck1)                  , (OT.Stuff2, BE.Buck2)                  , [...]            ) AS st(StuffValues,BucketExclude)	   WHERE BucketExclude = 1	   GROUP BY BucketExclude) AS ca1`

This is the main reason to use a CROSS APPLY: applying logic in ONE place rather than in multiple places.

Drew

J. Drew Allen

• Jeff Moden

SSC Guru

Points: 997127

This is really the same as a "Catch-All" query.  Why not write the dynamic SQL to do it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Brandie Tarvin

SSC Guru

Points: 172760

Why are you doing the `ISNULL()` check?  The `SUM()` will eliminate NULL values automatically.  Are you trying to eliminate the warning?

SUM() has NEVER eliminated NULL values automatically for me. It has always nulled out when trying to add NULL values. So I will not use a SUM without verifying that any NULL values become 0.00 for money (or whatever other value I need for other number datatypes).

On the note about the INNER JOIN causing a problem with OrderTypes not included, you have a good point. What I am doing here, though, is specifically pulling out the OrderTypes with excluded Stuff buckets into another table to figure out their totals. Then LEFT JOINing this staging table back to the main query which has the TotalStuff already for the included Stuff buckets. So using an INNER JOIN here will save me a minute at least and won't upset the balance of me getting TotalStuff for all other OrderTypes. ...Does this make sense to you? I'm rereading what I just typed and it may be confusing.

Anyway, in this table, I am doing an initial insert including ISNULL() for all money columns so if, when I do this part of the code, anything is still NULL, I've got bigger problems. Like, how did they slip through my ISNULL().

I do appreciate everyone's comments. This is helping me think through every possible "what could go wrong" scenario. If you have any other thoughts, please pass them along.

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.

Viewing 13 posts - 1 through 13 (of 13 total)