Getting column change history...

Question

Getting column change history...

PiMané

SSChampion

Points: 13965
More actions
January 15, 2016 at 9:40 am

#314937

Hi,
I've a table that stores the history from another table, all the records updates made.
I need to write a query to get just the price changes..
If I write the following query:
SELECT historyDate, price FROM history.vehicles WHERE idVehicle = 123 ORDER BY historyDate
I get the following result...
historyDateprice
2015-12-02 00:00:00.0005750
2015-12-06 00:00:00.0005750
2015-12-07 00:00:00.0005750
2015-12-08 00:00:00.0005750
2015-12-10 00:00:00.0005700
2015-12-13 00:00:00.0005650
2015-12-15 00:00:00.0004990
2015-12-17 00:00:00.0005000
2015-12-19 00:00:00.0005000
2015-12-20 00:00:00.0005000
2015-12-27 00:00:00.0004990
2016-01-02 00:00:00.0004990
2016-01-09 03:36:47.0004990
To get just the price changes I tried:
WITH data (
SELECT historyDate, price, row = ROW_NUMBER() OVER (PARTITION BY price ORDER BY historyDate) FROM history.vehicles WHERE idVehicle = 123
)
SELECT * FROM data WHERE row = 1 ORDER BY historyDate
But I get:
historyDatepricerow
2015-12-02 00:00:00.00057501
2015-12-10 00:00:00.00057001
2015-12-13 00:00:00.00056501
2015-12-15 00:00:00.00049901
2015-12-17 00:00:00.00050001
It's what I wrote but the price 4990 occurred again at 2015-12-27 and I'd want it to show the record: 2015-12-27 4990 to show...
I need to compare the next row price with the current row and if it's the same ignore.... How can I do that??!
Thanks,
Pedro

If you need to work better, try working less...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Have you considered putting 'lag' in your CTE, to get 'previous' price and then selecting from your CTE if price <> previous price?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

PiMané SSChampion Points: 13965 More actions · Answer 2

I changed the final CTE query to:

SELECT t0.* FROM data t0

LEFT JOIN data t1 ON t0.row - 1 = t1.row

WHERE t0.price <> t1.price OR t1.price IS NULL

ORDER BY t0.historyDate

and removed the PARTITION BY from que OVER clause..

and it worked...

But isn't there any faster way, with windows functions or something like ?!

Pedro

If you need to work better, try working less...

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

PiMané (1/15/2016)
I changed the final CTE query to:
SELECT t0.* FROM data t0
LEFT JOIN data t1 ON t0.row - 1 = t1.row
WHERE t0.price <> t1.price OR t1.price IS NULL
ORDER BY t0.historyDate
and removed the PARTITION BY from que OVER clause..
and it worked...
But isn't there any faster way, with windows functions or something like ?!
Pedro

Yes. See Phil's post above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PiMané SSChampion Points: 13965 More actions · Answer 4

Jeff Moden (1/15/2016)
PiMané (1/15/2016)
I changed the final CTE query to:
SELECT t0.* FROM data t0
LEFT JOIN data t1 ON t0.row - 1 = t1.row
WHERE t0.price <> t1.price OR t1.price IS NULL
ORDER BY t0.historyDate
and removed the PARTITION BY from que OVER clause..
and it worked...
But isn't there any faster way, with windows functions or something like ?!
Pedro
Yes. See Phil's post above.

Thanks Phil...

I didn't notice the link on the 'lag' word 🙂

If you need to work better, try working less...

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

My pleasure. Glad you got it working.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.