IS IT POSSIBLE IN SQL SERVER?

[lyudmila]

Hi all,
My table have just few fields. I need to compare price in current week to prior week, determine PRICE DROP and put flag for this record.
WK_END SKU PRICE
-------------------------------------
07/18/03 5565317330 1.29
07/25/03 5565317330 1.29
08/01/03 5565317330 1.29
08/08/03 5565317330 1.29
08/15/03 5565317330 1.29
08/22/03 5565317330 0.99
08/29/03 5565317330 0.99

My new table should look like that:
WK_END SKU PRICE PRICE_DROP_FLG
-------------------------------------------------------
07/18/03 5565317330 1.29
07/25/03 5565317330 1.29
08/01/03 5565317330 1.29
08/08/03 5565317330 1.29
08/15/03 5565317330 1.29
08/22/03 5565317330 0.99 DROP
08/29/03 5565317330 0.99


How this can be accomplished in transact-SQL?
Thank you.

 

[mrdenny]

You can use this to itentify and update the Price_Drop_Flg column. (This isn't tested so it may need some tweaking.)

update Table1
set Price_Drop_Flag = 'DROP'
from (select min(WK_END) WK_END, sku, price from Table1 group by sky, price) b
where Table1.WK_END = b.WK_END
   and Table1.sku = b.sku

I think this will work. If it doesn't let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[lyudmila]

It worked! Thank you so much, so simple and so briliant!

1)But I don't understand part of syntax, what means "select min(WK_END) WK_END," in subquerry?
(select min(WK_END) WK_END, sku, price from Table1 group by sky, price)?

2)Also I have a new chalenge - if PRICE in the fisrt week of analyzed period is less, then next week, this item consider to be on sale

my example should look like as follow

WK_END SKU PRICE PRICE_DROP_FLG
-------------------------------------------------------
07/18/03 5565317330 1.09 SALE
07/25/03 5565317330 1.29
08/01/03 5565317330 1.29
08/08/03 5565317330 1.29
08/15/03 5565317330 1.29
08/22/03 5565317330 0.99 DROP
08/29/03 5565317330 0.99

 

[mrdenny]

Cool, I'm glad it worked.

1) The "select min(WK_END) WK_END" means that we are pulling the first date with that SKU and Price, so that we can group by the sku and price, so that we have a unique date to join against.

2) Try a refersal of the code, I'm not sure how well this will work, but again, it's worth a shot.

update Table1
set Price_Drop_Flag = 'DROP'
from (select max(WK_END) WK_END, sku, price from Table1 group by sky, price) b
where Table1.WK_END = b.WK_END
   and Table1.sku = b.sku

Denny

--Anything is possible. All it takes is a little research. (Me)