Price change when updatedate

Transact-SQL
1

Hello all, i have question please help me solve

i have table sales

id  date      qty
01 2015-03-02 3
01 2015-03-12 5
02 2015-03-12 4
01 2015-03-13 2
02 2015-03-21 7

and table price

id  updatedate  price
01 2015-03-01   5000
01 2015-03-13   6000
02 2015-03-12   4500
02 2015-03-18   5500

and table type

id  descc
01  lamp
02  cable

now i want to show

id date      typee price qty total
01 2015-03-02 lamp  5000  3 15000
01 2015-03-12 lamp  5000  5 25000
01 2015-03-13 cable 6000  2 12000
02 2015-03-12 cable 4500  4 18000
02 2015-03-21 cable 5500  7 38500

explanation: example for id 1: 2015-03-02 price still 5000, when 2015-03-13 price change 6000, the price last assigned to each ID

any answer with subquery or left join? thx

2 
SELECT s.id, s.date, pr.price, t.descc, s.qty, s.qty * pr.price
FROM sales s
OUTER APPLY (
    SELECT TOP (1) price
    FROM prices p
    WHERE p.id = s.id AND p.date <= s.date
    ORDER BY p.date DESC
) AS pr
INNER JOIN type t ON t.id = s.id
1 Like
3

any different answer without using outer apply?

4

;WITH pricecte AS
(
SELECT id, updatedate as fromdate, price, row_number() over(PARTITION BY id ORDER BY dbo.price.updatedate ASC ) rno
from price
), pricectemaster AS (
SELECT p.id, p.price, p.fromdate, dateadd(dd,-1,p2.fromdate) todate
FROM pricecte p
LEFT OUTER JOIN pricecte p2
ON p.id= p2.id
AND p.rno +1 = p2.rno )

SELECT s.id, s.date, p.price, t.descc, s.qty, s.qty * p.price AS total
FROM sales s
INNER JOIN pricectemaster p
on p.id = s.id
AND s.date BETWEEN p.fromdate and isnull(p.todate , s.date)
INNER JOIN type t
ON t.id = s.id

5

Curious why you would not want to use OUTER APPLY? I've found it a very efficient approach to this type of problem (unless you are on a very old version of SQL :frowning: )

6

Select * from
(Select B.,A.SoldDate,A.Qty,C.PriceDate,A.QtyC.Price Total,
ROW_NUMBER() Over (Partition By A.Id,SoldDate Order By PriceDate Desc) RowNo
from Sales A
Inner join Product B On B.Id = A.Id
Inner join Price C On C.Id = A.Id And A.SoldDate>=C.PriceDate) Tbl
Where Tbl.RowNo = 1

1 Like
7

thx for the answer viggneshwar

but your last post

SELECT s.id, s.date, p.price, t.descc, s.qty, s.qty * p.price AS total
FROM sales s
INNER JOIN pricectemaster p
on p.id = s.id 
AND s.date BETWEEN p.fromdate and isnull(p.todate    , s.date)
INNER JOIN type t 
ON t.id = s.id

the result false can you fix it?

8

id SoldDate price ProdName qty total
1 2015-03-02 00:00:00.000 5000 lamp 3 15000
1 2015-03-12 00:00:00.000 5000 lamp 5 25000
1 2015-03-13 00:00:00.000 6000 lamp 2 12000
2 2015-03-12 00:00:00.000 4500 cable 4 18000
2 2015-03-13 00:00:00.000 4500 cable 7 31500

What is wrong in this output?
as per your sample output last line is differ because there is no rows to retrieve 2015-03-21 in sales table. Please explain

9

sorry i wrong input hehehe

i try in my sql server the output

02 2015-03-12 cable 4 4500 18000
01 2015-03-13 amp 2 6000 12000

using this query

SELECT s.id, s.date, p.price, t.descc, s.qty, s.qty * p.price AS total
FROM sales s
INNER JOIN pricectemaster p
on p.id = s.id 
AND s.date BETWEEN p.fromdate        and isnull(p.todate    , s.date)
INNER JOIN type t 
ON t.id = s.id