February 17, 2013 at 5:06 pm
I have the data below and I want to update the pricechange column for when the price changes for an invoice in my netprice column. The pricechange column can be created on the fly, it doesn't exists yet. I want the first(top) record on the invoice to be the price to compare with for each invoice.
orderid Invoice netprice pricechange
1234 98989 39.99
2222 98989 39.99
3333 98989 45.00 x
4444 98989 39.99
5343 98989 39.99
4321 98989 37.00 x
2313 98989 42.00 x
4545 98989 39.99
3211 98989 39.99
1122 77277 45.00
2332 77277 45.00
4455 77277 30.00 x
6989 77277 45.00
3493 77277 55.00 x
3434 77277 45.00
February 17, 2013 at 5:48 pm
Something like this perhaps?
;WITH MyData (orderid, Invoice, netprice, pricechange) AS (
SELECT 1234,98989,39.99,' '
UNION ALL SELECT 2222, 98989, 39.99,' '
UNION ALL SELECT 3333, 98989, 45.00, 'x'
UNION ALL SELECT 4444, 98989, 39.99,' '
UNION ALL SELECT 5343, 98989, 39.99,' '
UNION ALL SELECT 4321, 98989, 37.00, 'x'
UNION ALL SELECT 2313, 98989, 42.00, 'x'
UNION ALL SELECT 4545, 98989, 39.99,' '
UNION ALL SELECT 3211, 98989, 39.99,' '
UNION ALL SELECT 1122, 77277, 45.00,' '
UNION ALL SELECT 2332, 77277, 45.00,' '
UNION ALL SELECT 4455, 77277, 30.00, 'x'
UNION ALL SELECT 6989, 77277, 45.00,' '
UNION ALL SELECT 3493, 77277, 55.00, 'x'
UNION ALL SELECT 3434, 77277, 45.00,' '
),
FirstOrder AS (
SELECT orderid, invoice, netprice
FROM (
SELECT orderid, invoice, netprice
,n=ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY orderid)
FROM MyData) a
WHERE n=1
)
SELECT a.orderid, a.invoice, a.netprice, a.pricechange
,mypricechange=CASE WHEN a.netprice <> b.netprice THEN 'x' ELSE ' ' END
FROM MyData a
JOIN FirstOrder b ON a.invoice = b.invoice
ORDER BY a.Invoice, a.orderid
You can also do it with a correlated subquery.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2013 at 6:22 pm
Haven't tested it but looks like this is what I need. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply