asked on

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

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

Kent Dyer

Can you show us what you have already?

Thanks,

Kent

MikeBinNC

ASKER

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

Member_2_861731

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.

MikeBinNC

ASKER

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

Member_2_861731

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.

MikeBinNC

ASKER

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

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeBinNC

ASKER

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