April 8, 2009 at 12:56 am
hey,
i have a table for my products with these columns
Product:ProductId , BuyFee , SaleFee
that holds the latest price of a product
and another table for saving the history of price changes like this
ProductHistory:ProductId,Date,BuyFee,SaleFee
I need a query to get all of the product with now and previous prices like these columns
ProductId , LastBuyFee , PreviousBuyFee,LastSaleFee , PreviousSaleFee
can anyone help me on that!!
April 8, 2009 at 2:52 am
Is the row inserted into history table immediately when a new price is set, or only after the price loses validity? In other words, is the current price in the history table or not? Do you want to display only the current and last before current, or entire history of prices?
It would also help if you post table structure as CREATE TABLE statements, and some example data in form of INSERT INTO statements, plus required resultset for this example.
April 8, 2009 at 3:20 am
Hi,
try this
declare @abc table (
PID int,
date1 datetime,
BuyFee int,
SaleFee int
)
insert into @abc values(1,'2009-04-08',101,505)
insert into @abc values(1,'2009-04-07',99,500)
insert into @abc values(2,'2009-04-05',101,505)
insert into @abc values(2,'2009-04-04',99,499)
insert into @abc values(2,'2009-04-03',100,500)
insert into @abc values(2,'2009-04-03',100,500)
select a.pid,
(select top 1 b.BuyFee from @abc as b where b.PID = a.PID order by b.date1 desc )LastBuyFee ,
(select top 1 c.SaleFee from @abc as c where c.PID = a.PID order by c.date1 desc )LastSaleFee ,
(select top 1 d.BuyFee from @abc as d where d.PID = a.PID and d.BuyFee not in(select top 1 b.BuyFee from @abc as b where b.PID = a.PID order by b.date1 desc )
order by d.date1 desc )PreviousBuyFee ,
(select top 1 c.SaleFee from @abc as c where c.PID = a.PID and c.SaleFee not in (select top 1 c.SaleFee from @abc as c where c.PID = a.PID order by c.date1 desc )
order by c.date1 desc )PreviousSaleFee
from @abc as a
group by a.pid
RESULT
pid LastBuyFee LastSaleFee PreviousBuyFee PreviousSaleFee
1 101 505 99 500
2 101 505 99 499
ARUN SAS
April 8, 2009 at 4:12 am
hey i think i found my solution:
CREATE VIEW UV_ProductsPrices
AS
WITH PriceCompare AS (
SELECT i.productid ,ph.actiondate,ph.feebuy,ph.feesale,ph.feeenduser,ROW_NUMBER() OVER (Partition BY ph.ProductId ORDER BY ActionDate) AS rownum
FROM Trade.Product i
INNER JOIN Trade.ChangeFeeHistory ph
ON i.ProductId = ph.ProductId)
SELECT currow.productid,
currow.feebuy AS BuyFee,
currow.feeSale AS SaleFee,
currow.feeEndUser AS EndUserFee,
prevrow.feebuy AS OldBuyFee,
prevrow.feeSale AS OldSaleFee,
prevrow.feeEndUser AS OldEndUserFee,
currow.actiondate AS StartDate,
nextrow.ActionDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ProductId = nextrow.ProductId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.productId = prevrow.productId
thanks for your response
regards
April 8, 2009 at 8:14 am
aram_golbaghi (4/8/2009)
hey i think i found my solution:CREATE VIEW UV_ProductsPrices
AS
WITH PriceCompare AS (
SELECT i.productid ,ph.actiondate,ph.feebuy,ph.feesale,ph.feeenduser,ROW_NUMBER() OVER (Partition BY ph.ProductId ORDER BY ActionDate) AS rownum
FROM Trade.Product i
INNER JOIN Trade.ChangeFeeHistory ph
ON i.ProductId = ph.ProductId)
SELECT currow.productid,
currow.feebuy AS BuyFee,
currow.feeSale AS SaleFee,
currow.feeEndUser AS EndUserFee,
prevrow.feebuy AS OldBuyFee,
prevrow.feeSale AS OldSaleFee,
prevrow.feeEndUser AS OldEndUserFee,
currow.actiondate AS StartDate,
nextrow.ActionDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ProductId = nextrow.ProductId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.productId = prevrow.productId
thanks for your response
regards
kool....;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply