Ytukyg

I have a table that contains 4 columns:

ItemId, ItemPrice, DateFrom, DateTo

The table contains information about items, their prices and the day that the prices changed

for example:

What I want to do, is to fill the DateTo column so I know what for how long the price has lasted and if it still lasts

The results I expect:

An important note is also that the count of the price changes is unknown, for some items it changes 3 times and for some it doesn't change at all and the price remains unchanged until this day.

Any kind of help will be greatly appreciated.

A view would be far better solution to this, as there's no need to have a AFTER UPDATE trigger, and you can't persist a column with the value GETDATE() (thus you would have to update the value of DateTo at the start of every day). This will get you the result you are after:

CREATE VIEW YourView AS



    SELECT ItemId,

           ItemPrice,

           DateFrom,

           LEAD(DATEADD(DAY,-1,DateFrom),1,GETDATE()) OVER (PARTITION BY ItemId ORDER BY DateFrom ASC) AS DateTo

    FROM YourTable;

Try below query - Check it online

DECLARE @Tab TABLE

(

        itemid INT,

        itemprice INT,

        DateFrom Date

)



INSERT INTO @Tab VALUES (111,5000,'2018-01-01'),(111,8000,'2018-02-24'),(111,6000,'2018-03-12')



SELECT *,ISNULL(

        (SELECT top 1 DATEADD(day,-1,DateFrom) 

            from @tab t1 

            WHERE t1.itemid= t2.itemid

            AND t1.Datefrom > t2.Datefrom) , GETDATE()) AS DateTo 

FROM @tab t2

You need to set the DateTo of each record to "the smallest of all From-dates (for the same ItemId) that are bigger than its own DateFrom, minus 1 day".

And for those where it couldn't be found, set it to GetDate().

In SQL:

UPDATE PriceTable

SET    DateTo = (SELECT DATEADD(d, -1, MIN(DateFrom))

                 FROM   PriceTable P2

                 WHERE  P2.ItemId   = P1.ItemId

                 AND    P2.DateFrom > P1.DateFrom)

FROM PriceTable P1



UPDATE PriceTable

SET    DateTo = GETDATE()

WHERE  DateTo IS NULL

Or combined into one step:

UPDATE PriceTable

SET    DateTo = (SELECT ISNULL(DATEADD(d, -1, MIN(DateFrom)), GETDATE())

                 FROM   PriceTable P2

                 WHERE  P2.ItemId   = P1.ItemId

                 AND    P2.DateFrom > P1.DateFrom)

FROM PriceTable P1

You can create date_to column on adhoc basic as you see. below

SELECT ItemId, ItemPrice, DateFrom,

DATEADD(day, 1, lead(DateFrom,1,getdate()))  

over (order by ItemId asc ,DateFrom asc) as date_to 

FROM tbl 

You can update value as

UPDATE tbl T2 SET dateto= 

(SELECT 

DATEADD(day, 1, lead(DateFrom,1,getdate()))  over (order by ItemId asc ,DateFrom asc) 

FROM tbl T1 where T1.ItemId=T1.ItemId AND  T1.ItemId = T2.ItemId AND  T1.DateFrom = 

T2.DateFrom)

For future add trigger who update dateTo on insert new row, now you must select previous value DateFrom

SELECT ItemId, ItemPrice, DateFrom,

 SELECT(DateFrom FROM tbl WHERE ItemId IN (

   SELECT TOP 1 ItemId FROM tbl WHERE tbl.ItemId = tbl1.ItemId 

     WHERE tbl.DateFrom < tbl1.DateFrom 

     ORDER BY tbl.DateFrom DESC)

) FROM tbl AS tbl1