I am having difficultly working the logic out for this -
I have this table:
P S D
StoreA Widget1 1 1 1
StoreB Widget1 3 5 2
StoreC Widget1 2 3 3
StoreD Widget1 2 2 4
StoreA Widget2 3 3 1
StoreB Widget2 2 3 2
StoreC Widget2 2 3 3
StoreD Widget2 1 2 4
StoreA Widget3 1 2 1
StoreB Widget3 2 5 2
StoreC Widget3 2 2 3
StoreD Widget3 2 2 4
StoreA Widget4 3 3 1
StoreB Widget4 2 2 2
StoreC Widget4 2 3 3
StoreD Widget4 1 3 4
I need to create a table from the above source table that tells me which store to buy each widget (1,2,3,4...there are 100s of widgets) from based first on lowest price (first column) if that is the same at more than one store for the widget then the next deciding factor would be lowest shipping cost (second column) for the widget if that’s the same at more than one store then the least amount of days (third column) it takes to ship would break the tie. I am on SQL 2000 and 2005 boxes. There can be many more than 4 stores and many more than 4 widgets. For each column the value of 1 is the most favorable and the value of 4 is the least favorable
Thanks so much for taking the time!
I have this table:
P S D
StoreA Widget1 1 1 1
StoreB Widget1 3 5 2
StoreC Widget1 2 3 3
StoreD Widget1 2 2 4
StoreA Widget2 3 3 1
StoreB Widget2 2 3 2
StoreC Widget2 2 3 3
StoreD Widget2 1 2 4
StoreA Widget3 1 2 1
StoreB Widget3 2 5 2
StoreC Widget3 2 2 3
StoreD Widget3 2 2 4
StoreA Widget4 3 3 1
StoreB Widget4 2 2 2
StoreC Widget4 2 3 3
StoreD Widget4 1 3 4
I need to create a table from the above source table that tells me which store to buy each widget (1,2,3,4...there are 100s of widgets) from based first on lowest price (first column) if that is the same at more than one store for the widget then the next deciding factor would be lowest shipping cost (second column) for the widget if that’s the same at more than one store then the least amount of days (third column) it takes to ship would break the tie. I am on SQL 2000 and 2005 boxes. There can be many more than 4 stores and many more than 4 widgets. For each column the value of 1 is the most favorable and the value of 4 is the least favorable
Thanks so much for taking the time!