[thelist] query help (ms sql 2000)

Luther, Ron Ron.Luther at hp.com
Fri Jan 12 11:10:46 CST 2007

Brian Cummiskey asked an interesting SQL question about trying to pull
price tier info:

>>another example:
>>buy 1-3, $19.99
>>buy 4-5, $15.99 each
>>buy 6 or more, $14.99 each

>>item456, 1, 2, 3, 4, 5, 9999999, 0, 0, 0, 1999, 1999, 1999, 1599,
1499, 0, 0, 0

Hi Brian,

Neat problem!  ;-)

Question:  Do the quantity fields always start with one and increment by

I believe that, if true, this would make the quantity information
redundant so there would be no loss of information if you ignored those
nine fields and only considered the price info instead.

I'm pretty sure that each db contains commands, (quite possibly combined
in long ugly nested constructs), that would let you determine the last
"or more" price, the number of unique prices (price tiers), and the
change points.  I'm not seeing a simple way to get them all in a single
pass as yet and you will still need a loop from 1 to the number of price
tiers in order to print them out nicely ... But maybe this helps a
little bit.  <shrug />


{I'm not sure why but I keep thinking you should transform the price
table into a 'price table ii' and structure it as some kind of tree or
bill of materials where each record kinda looks like this:  [item
number], [number of price tiers], [current tier number], [tier start
qty], [tier end qty], [tier price].  You could run the transform script
once a day or whenever you get new price data to load. Doing the heavy
lifting offline like this could reduce production load and improve
response time.}

More information about the thelist mailing list