January 14, 2020 at 9:09 pm
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?
January 14, 2020 at 9:31 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2020 at 9:45 pm
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.
January 14, 2020 at 10:05 pm
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
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2020 at 10:23 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2020 at 10:35 pm
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
January 16, 2020 at 11:51 am
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.
January 16, 2020 at 4:47 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 16, 2020 at 6:34 pm
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.
January 16, 2020 at 6:46 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2020 at 8:24 pm
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 ca1Thanks 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
January 16, 2020 at 11:05 pm
This is really the same as a "Catch-All" query. Why not write the dynamic SQL to do it?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2020 at 12:17 pm
Why are you doing the
ISNULL()check? TheSUM()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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply