Microsoft SQL Server 2008 - query to check totals add to 100% and then fix if necessary

MikeBinNC
MikeBinNC used Ask the Experts™
on
I am trying to write a query in Microsoft SQL Server 2008 R2.  I have the following demand information (columns): product, qty, source site, destination site.  There are multiple products, sources, and destinations and the table represents 1 year worth of orders

I want to do the following (I can easily do steps 1 and 2):
1. find the % of the total quantity provided by each source site by product to each destination site (so…if two sources one might have provided 80% of product A and the other source the remaining 20%).
2. round this % to three decimal places
3. Then check that the total % for each source site for each product adds up to 100% exactly....as the rounding could cause this to NOT be the case.
4. IF the total is not exactly 100% add the delta  (add or subtract) to the source site and product pair with the largest % (using example from above: if total is 99.999 then I need to add 0.001 to the 80% value to get 80.01.

Would like the entire SQL code to do this....does not need to be the most efficient solution (i.e. multiple steps/tables is OK) long as I can follow the steps that were taken.

For the code: TABLE A
columns: PRODUCT, QTY, SOURCE, DESTINATION

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
Can you show us what you have already?

Thanks,

Kent

Author

Commented:
Here is what I can easily post...the image below shows excel sample of the output after STEP 2 above....you can see by the total that after rounding we have less than 100%...so I want to add this delta (0.001) to the largest percentage (89.177 would become 89.178).  Of course, the file has many more lines.  When I try to do this I cannot seem to get the GROUP BY correct to ensure I group by destination and product...find the largest % , and then add the delta to the %. I would do this for all destination and product groups..  


                  
            

100-PCT-check-image.PNG
Could you provide us with the table structure to know what columns we should group by? Is all the data coming from the same table? Do we have the order total? Or must we calculate it?
I'm guessing this would require more than one query to execute, good thing you say that's not a big deal.

Author

Commented:
All data is in the same table.  I have taken the first few steps to convert the raw data (which isat the order level)  and have grouped by source, destination, and product and then found the % show above and rounded to 3 decimals.  

If you all can help with the above as the starting point that is fine with me...if you want order data it would look like the above except column D with have a quantity as opposed to a percent and there potentially multiple lines from orders on different days that need summed.

Regarding the use of multiple queries....it seems like it would take me (a real ameteur) many queries to make this happen...you all do as many as you think is needed.

Thanks for all the help
I understand what you need, but in order for us to "create" the query we would need at least the table structure to know the columns we're gonna be using.

If you don't wanna share the real information, you can provide us with fictitious data.

Author

Commented:

Here you go....This is order level detail - and represents the columns in the table.  So, I need the following:
1. find the % of the total quantity provided by each source site by product to each destination site.
2. round this % to three decimal places
3. Then check that the total % for each source site for each product adds up to 100% exactly.
4. IF the total is not exactly 100% add the delta  (add or subtract) to the source site with the largest %
Hope this is what you need...if not happy to try again

columns:
PRODUCT
SOURCE
DESTINATION
QUANTITY
Ok, I built something up for you... Hopefully I understood correctly and it works for your situation.
I know this might not be the most elegant/efficient solution, but it works.

I use temp tables for holding most of the temp data, and the sample data is inserted into a table variable. You might need to tweak the query to put in your real table names and fields, but the logic should work.

Let me know if this is what you need.
-- TABLE VARIABLE TO HOLD SAMPLE DATA
DECLARE @myTable TABLE (PRODUCT VARCHAR(20),
SOURCE VARCHAR(20),
DESTINATION VARCHAR(20),
QUANTITY NUMERIC(10,3))

-- INSERTING SOME SAMPLE DATA
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 1', 200)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 1', 10)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 1', 37)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 1', 29)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 1', 19)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 1', 13)

INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 2', 200)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 2', 5.107)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 2', 37)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 2', 29)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 2', 19)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 2', 13)

INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 3', 10)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG A', 'CUST 3', 20)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 3', 30)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG B', 'CUST 3', 40)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 3', 5)
INSERT INTO @myTable(PRODUCT,SOURCE,DESTINATION,QUANTITY)
VALUES('AC UNIT', 'MFG C', 'CUST 3', 10)

-- THIS IS WHERE THE REAL PROCEDURE STARTS

IF OBJECT_ID('tempdb..#tmpTotal') IS NOT NULL DROP TABLE #tmpTotal
IF OBJECT_ID('tempdb..#tmpMax') IS NOT NULL DROP TABLE #tmpMax
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmpNot100') IS NOT NULL DROP TABLE #tmpNot100
DECLARE @dif NUMERIC(4,3)

-- THIS HOLDS THE SUM OF QUANTITY FOR EACH DESTINATION/PRODUCT
SELECT destination, product, sum(quantity) TOTAL
INTO #tmpTotal
FROM @myTable
GROUP BY destination, product

-- THIS HOLDS THE PERCENTAGE FOR EACH SOURCE/PRODUCT/DESTINATION
SELECT a.source, a.destination, a.product, ROUND(SUM(a.quantity/b.total*100),3) percentage
INTO #tmp
FROM @myTable a INNER JOIN #tmpTotal b ON
a.destination=b.destination AND a.product=b.product
GROUP BY a.source, a.destination, a.product

-- THIS HOLDS THE MAXIMUM PERCENTAGE FOR EACH DESTINATION/PRODUCT
SELECT destination, product, MAX(percentage) maximum
INTO #tmpMax
FROM #tmp
GROUP BY destination, product

-- THIS HOLDS ALL THE DESTINATION/PRODUCTS THAT DON'T ADD 100%
SELECT destination, product, SUM(percentage) GT
INTO #tmpNot100
FROM #tmp
GROUP BY destination, product
HAVING SUM(percentage)<>100

-- THIS UPDATES THE PERCENTAGE WITH THE MAX(%) FOR EACH PRODUCT/DESTINATION THAT DOESN'T ADD 100%
UPDATE t1
SET t1.percentage=t1.percentage+(100-t3.gt)
FROM #tmp t1 INNER JOIN #tmpMax t2 ON
t1.destination=t2.destination AND t1.product=t2.product 
AND t1.percentage=t2.maximum INNER JOIN #tmpNot100 t3 ON
t1.destination=t3.destination AND t1.product=t3.product

-- THIS SHOWS THE FINAL DATA
SELECT * FROM #tmp 

Open in new window

Author

Commented:
thanks to everyone for the help...the final solution was easy to follow even for a newbie like me.  
THANKS MUCH

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial