Unanswered: Calling Experts - Need to do some distribution
I have a table called SalesData containing a few thousand records. This table stores sales related information and has the following fields:
2) ProdCategory (Pls. don't ask why I have this field over here)
Please note that this table is not directly populated from my OLTP but is the end results of some transformations etc... For some selective product I have breakup/distribution related details available in an Excel file. For example, for Product XYZ beloging to Category Toys, certain portion of the SalesAmount and QtySold is to be distributed/allocated to another Product belonging to a different category...
And here's the challenge which needs to be done for ALL records in my SalesData table:
1) I start traversing through my table starting from the first record and come across a record for a product which has to be distributed (i.e. it's one of the products in my Excel file). Let's say this is Product ABC, belongs to Category C1 and let's call this found record as record # 1.
2) At this point I have to a add a new record in my table (i.e. duplicate record #1)
3) In the new record, the ProdCategory and the ProductName will have to be changed as per the details available in my Excel file. Suppose that in this Excel file, the factor (for distribution/allocation) for Product ABC belonging to Category C1 is 3 and the Product and Category to which certain portion (i.e. 3% in this case) is to be distributed/allocated is Product XYZ belonging to Category C4
4) I have to allocate 3% of the original SaleAmount and QtySold of record #1 in the newly duplicated record and also have to change the Product from ABC to XYZ and also have to change the category from C1 to C4
5) The SaleAmount and QtySold in record #1 will have to be 3% less than what it originally was
I do realize that this is probably a challenging one and converting my Excel data into a 2 dimension "table" in SQL server may also be somewhat challenging???
Assuming ( i really hope so) my question is clear, can someone please help me achieve this? I will be happy to answer any questions that you may have to get 100% clarity regarding my question/requirement.