Calculating totals based on a column flag

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    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: 98285

    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 OT
    INNER 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: 172707

    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 3188

    SSC Guru

    Points: 88215

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

    SELECT 
    [...],
    ca1.TotalStuff
    FROM #OrigTable OT
    INNER 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

     

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • ScottPletcher

    SSC Guru

    Points: 98285

    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 OT
    INNER 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: 76686

    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 ot
    CROSS JOIN #BucketExclude AS be
    CROSS 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
    Business Intelligence Analyst
    Philadelphia, PA

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    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 OT
    SET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)
    FROM #OrigTable OT
    INNER JOIN #JoinTableIForgot JT
    ON OT.OrderID = JT.OrderID
    INNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderType
    CROSS 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: 98285

    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 OT
    SET
    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 OT
    INNER 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: 172707

    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 OT
    SET
    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 OT
    INNER 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 3188

    SSC Guru

    Points: 88215

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • drew.allen

    SSC Guru

    Points: 76686

    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 OT
    SET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)
    FROM #OrigTable OT
    INNER JOIN #JoinTableIForgot JT
    ON OT.OrderID = JT.OrderID
    INNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderType
    CROSS 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 OT
    SET AdjustedStuff = ISNULL(OT.TotalStuff,0.00) - ISNULL(ca1.AdjustStuff,0.00)
    FROM #OrigTable OT
    INNER JOIN #JoinTableIForgot JT
    ON OT.OrderID = JT.OrderID
    INNER JOIN #BucketExclude BE ON JT.OrderType = BE.OrderType
    CROSS 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
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 995457

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

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

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    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.

    • This reply was modified 17 hours, 9 minutes ago by  Brandie Tarvin.

    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)

You must be logged in to reply to this topic. Login to reply