sql-query that change all validTo dates to the next validFrom date minus one Day
I have to modify a big pricelist table so that there is only one valid price for every article.
Sometimes the sales employees insert new prices and forgot to change the old infinite validTo dates.
So I have to write a sql-query to change all validTo dates to the next validFrom date minus one day, when the validTo date has infinite validity (9999-12-31).
But I have no idea how can i reach this with only SQL (Oracle 12).
This data represents the same as in the explicit time spans example. The first price is valid before January 16, then the second price is valid until January 17, and afterwards the next price (which equals the first price again) is valid. Here you don't need an EndDate, because it's implicit. Of course the first price is only valid until January 15, because from January 16 there is another price valid (record #2).
So: Either remove the EndDate column completely or let it untouched. Don't simply update it, as you have intended. If you updated your records to next date minus one, you would actually hold data redundantly, which might lead to problems later.
Oracle provides an analytic function LEAD that references the current-plus-n-th record given a sort criterion. This function may serve the purpose of selecting the proper date value in an update statement as follows ( let test_prices be the table name, ppk its PK ):
update test_prices p
set p.validTo = (
select lead ( p1.validFrom, 1 ) over ( order by p1.validFrom ) - 1 vtn
from test_prices p1
where ps.ppk = p.ppk
where to_char(p.validTo, 'YYYY') = '9999'
and p.validFrom != ( select max(validFrom) from test_prices )
UPDATE VALID_DATES v
SET validTo = (
LEAD( validFrom - 1, 1 ) OVER ( PARTITION BY anr ORDER BY validFrom ),
) AS validTo
WHERE v.anr = u.anr
AND v.validFrom = u.validFrom
WHERE validTo = DATE '9999-12-31';
In order to update an end date you can simply select the minimum of all higher start dates.
update mytable upd
set enddate = coalesce(
select min(startdate) - 1
from mytable later
where later.startdate > upd.startdate
and later.anr = upd.anr -- same product
), date'9999-12-31') -- coalesce for the case there is no later record
where enddate = date'9999-12-31';
I have taken anr to be the product id. If it isn't then change the statement accordingly.
Asked in February 2016Viewed 2,933 timesVoted 6Answered 4 times