troubleshooting Question

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

Avatar of MikeBinNC
MikeBinNC asked on
Microsoft SQL ServerMySQL Server
8 Comments1 Solution315 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros