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