CODEWITHSUNDEEP
sqlsql-server
Surya
Surya

Reputation: 3

Get data based on changes between datetime SQL

I have a table called price_history; this table stored a price change between date.

+-------------------------+-----------+-----------+-----------+
| update_timestamp        | item_code | old_price | new_price |
+-------------------------+-----------+-----------+-----------+
| 2019-11-04 19:50:33.857 | XA00001   | 194357.47 | 193891.77 |
| 2019-11-03 21:53:01.910 | XA00001   | 193892.19 | 194357.47 |
| 2019-11-02 21:09:22.687 | XA00001   | 194357.47 | 193892.19 |
| 2019-11-02 05:33:39.563 | XA00001   | 194357.47 | 194357.47 |
+-------------------------+-----------+-----------+-----------+

and I also have table called item_transaction.

+-------------------------+-----------+-----------+
| sell_timestamp          | item_code | qty       |
+-------------------------+-----------+-----------+
| 2019-11-04 19:56:34.000 | XA00001   | 1         |
| 2019-11-03 21:54:29.000 | XA00001   | 2         | 
| 2019-11-02 21:11:36.000 | XA00001   | 2         |
| 2019-11-02 12:22:43.000 | XA00001   | 1         |
| 2019-11-02 12:22:42.000 | XA00001   | 1         |
| 2019-11-02 12:21:44.000 | XA00001   | 1         |
| 2019-11-02 05:34:43.000 | XA00001   | 1         |
+-------------------------+-----------+-----------+

I'm looking for a query to get a price from price_history based on sell_timestamp from item_transaction.

This is the result I want:

+-------------------------+-----------+-----------+-----------+
| sell_timestamp          | item_code | qty       | price     |
+-------------------------+-----------+-----------+-----------+
| 2019-11-04 19:56:34.000 | XA00001   | 1         | 193891.77 |
| 2019-11-03 21:54:29.000 | XA00001   | 2         | 194357.47 |
| 2019-11-02 21:11:36.000 | XA00001   | 2         | 193892.19 |
| 2019-11-02 12:22:43.000 | XA00001   | 1         | 194357.47 |
| 2019-11-02 12:22:42.000 | XA00001   | 1         | 194357.47 |
| 2019-11-02 12:21:44.000 | XA00001   | 1         | 194357.47 |
| 2019-11-02 05:34:43.000 | XA00001   | 1         | 194357.47 |
+-------------------------+-----------+-----------+-----------+

I'm only able to create a query to get a price on the same date as price changed date.

SELECT 
    a.sell_timestamp, a.item_code, a.qty, b.new_price AS price
FROM 
    item_transaction a
LEFT JOIN 
    price_history b ON a.item_code = b.item_code 
                    AND CAST(a.sell_timestamp AS DATE) = CAST(b.update_timestamp AS DATE)
WHERE 
    CAST(a.sell_timestamp, AS DATE) BETWEEN '2019-11-02' AND '2019-11-04'

Upvotes: 0

Views: 111

Answers (3)

MeirS
MeirS

Reputation: 1

You can use "Lead" function for getting the timestamp range the price was valid and then look for records in the time range

select sell_timestamp, item_code, qty,
        (
            select new_price 
               from(select update_timestamp,
                           isnull(lead(update_timestamp) over(order by update_timestamp),getdate()) until,
                           new_price
                    from price_history) a
                   where it.sell_timestamp between a.update_timestamp and a.until
        ) price
  from item_transaction it

Upvotes: 0

charles
charles

Reputation: 146

My solution:

  1. Get all the previous price time intervals:

    SELECT a.*, b.new_price as price, DATEDIFF(ms,b.update_timestamp,a.sell_timestamp) AS interval 
FROM item_transaction a 
INNER JOIN price_history b ON a.sell_timestamp>b.update_timestamp

  2. Order all result in step 1 with row number partition by sell_timestamp(better by item_transaction's primary key if you have) and order by interval:

    SELECT *, ROW_NUMBER() over(partition by sell_timestamp order by interval) as rn 
FROM (
    SELECT a.*, b.new_price as price, DATEDIFF(ms,b.update_timestamp,a.sell_timestamp) AS interval 
    FROM item_transaction a 
    INNER JOIN price_history b ON a.sell_timestamp>b.update_timestamp
) temp

  3. Filter result of step 2 with condition row number equals 1:

    SELECT sell_timestamp, item_code, qty, price 
FROM(
    SELECT *, ROW_NUMBER() over(partition by sell_timestamp order by interval) as rn 
    FROM (
        SELECT a.*, b.new_price as price, DATEDIFF(ms,b.update_timestamp,a.sell_timestamp) AS interval 
        FROM item_transaction a 
        INNER JOIN price_history b ON a.sell_timestamp>b.update_timestamp
    ) temp
) temp2 where rn=1

The final query is:

SELECT sell_timestamp, item_code, qty, price 
FROM(
    SELECT *, ROW_NUMBER() over(partition by sell_timestamp order by interval) as rn 
    FROM (
        SELECT a.*, b.new_price as price, DATEDIFF(ms,b.update_timestamp,a.sell_timestamp) AS interval 
        FROM item_transaction a 
        INNER JOIN price_history b ON a.sell_timestamp>b.update_timestamp
    ) temp
) temp2 where rn=1

Upvotes: 0

Harlo
Harlo

Reputation: 507

To make things easier, I would suggest you table price_history should store DateFrom and DateTo to keep track on the effective date time range. Now, what I have tired is build a temp view by using WITH clause, to make the price_history to show in DateFrom and DateTo, and then join to table item_transaction and should be able to get your expected result. Here is my query, please have a try.

;WITH price_history_data AS
(
    SELECT Row_Number() OVER (PARTITION BY item_code ORDER BY update_timestamp) row_num,
            item_code, update_timestamp, new_price
    FROM price_history 
),
price_history_from_to AS
(
    SELECT phd.update_timestamp AS DateFrom,
            COALESCE(phd2.update_timestamp, GETDATE()) DateTo,
            phd.new_price,
            phd.item_code
    FROM price_history_data phd
    LEFT JOIN price_history_data phd2 ON (phd.row_num = phd2.row_num-1 AND phd.item_code = phd2.item_code)
)
SELECT it.sell_timestamp, it.item_code, it.qty, phft.new_price AS price, phft.DateFrom
FROM item_transaction it
LEFT JOIN price_history_from_to phft
ON (it.item_code = phft.item_code AND it.sell_timestamp >= phft.DateFrom AND it.sell_timestamp < phft.DateTo)
ORDER BY it.sell_timestamp DESC

Upvotes: 1

Related Questions