April 10, 2008 at 6:32 pm
Hello,
First off, I'm new to using SSIS (I just started messing around with it this week). I've got something that I think can be done using SSIS, but am not quite sure how to accomplish it. I would like to say that I'm pretty good with SQL, but then again I don't do
Here's what I'm trying to do....
I've got a data warehouse that holds weekly on hand quantities for some items at some store locations. What I need to do is find out what the retail dollar amount of these on hand units by week for 2007. This part shouldn't be that difficult to do, but this next part is where I'm trying to figure out what's the best way to do it.... Let's say in weeks 1 and 2 the retail price for item 1001 is 29.95, but in week 3 the item was put on sale for 25.99, then in week 25 it was discounted even more to 20.97.
Here's the data that I currently have:
inventory table: item, original price, current price. --pretty straight forward... every item with original price, and what the current price is.
markdown table: week, item, old price, new price --The records in this table include only items that have had their price adjusted (could be up or down, mostly down though) and what week is was discounted (there will be multiple records for items that have been discounted twice).
on hand table: week, store, item, on hand qty. --This is pretty straight forward too... basically how much of an item did the store have on hand for each week.
The part I'm really struggling with is how to get the correct price for each week. Do I run a loop and update the current_price in my inventory table and do this going backwards? i.e. start with week 52, go through the records in the markdown table and for every item update the inventory table and set the inventory.current_price = markdown.new_price, then calculate the retail price for that week. Then after doing the calculation update the current_price to the markdown.old_price so that for week 51 it's doing the next week's calculation based on what the price would have been for that week.
Logically I think the above would work, but I'm not sure how to do it using SSIS. Or should I be using a different tool to get this info?
Any help, tips, pointing me in the right direction, is much appreciated.
Thanks,
Jared
April 17, 2008 at 12:33 pm
Jared,
I think you're going to have to store the point in time pricing as well. Normalizing data is a great idea, but sometimes it doesn't work. Ex. customer record contains the normal shipping address, but you do have to repeat that data in the actual order information, as the customer might ship it to somewhere else, and you have to keep the data for tracing the package if it's lost.
If you don't have a point in time sort of record, you'll have to manufacture it like you're talking about for the data warehouse to pull against. Can you add it to the onhand table? makes sense to have it there if the stores don't always have the same price, and you're already capturing the specific data from each store there on the qty anyway.
Look at the Foreach loop container or the for loop container for doing the looping within the job itself.
Brenda
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply