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

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

MySQL ServerMicrosoft SQL Server

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.

I'm guessing this would require more than one query to execute, good thing you say that's not a big deal.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

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

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.

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

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

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

THANKS MUCH

THANKS MUCH

Thanks,

Kent