January 30, 2014 at 10:41 pm
drop table ProductHistory
CREATE TABLE ProductHistory
(
ProductName varchar(20),
Price float,
StartDate datetime,
EndDate datetime,
FlagActivePrice smallint
)
insert into ProductHistory values
('Pen',0,'01/15/2014 4:13','01/16/2014 6:15',0),
('Pen',0,'01/16/2014 6:15','12/31/9999 0:00',1),
('Book',22.57,'01/14/2014 3:15','01/15/2014 7:12',0),
('Book',23.27,'01/15/2014 7:12','01/17/2014 8:26',0),
('Book',23.27,'01/17/2014 8:26','12/31/9999 0:00',1),
('Laptop',405.29,'01/17/2014 8:00','12/31/9999 0:00',1),
('iPhone',550.29,'01/13/2014 7:15','01/14/2014 2:28',0),
('iPhone',550.29,'01/14/2014 2:28','01/15/2014 6:00',0),
('iPhone',490.68,'01/15/2014 6:00','01/17/2014 8:00',0),
('iPhone',490.68,'01/17/2014 8:00','01/18/2014 5:00',0),
('iPhone',490.68,'01/18/2014 5:00','01/21/2014 7:36',0),
('iPhone',490.68,'01/21/2014 7:36','12/31/9999 0:00',1)
Expected output as following:
try to store history of price of product. but if price is same then those records need not pull. Eg. Pen's both price was 0. I do not need both records. but we have data of Pen's from '01/15/2014 4:13' so put that as startdate and end date is '12/31/9999 0:00' and this is flagactive should be 1. Where as in Book's second and third have same price so one need to delete but startdate need to update.
ProductName Price StartDate EndDate FlagActivePrice
Pen 0 2014-01-15 04:13:00.000 9999-12-31 00:00:00.000 1
Book 22.57 2014-01-14 03:15:00.000 2014-01-15 07:12:00.000 0
Book 23.27 2014-01-15 07:12:00.000 9999-12-31 00:00:00.000 1
Laptop 405.29 2014-01-17 08:00:00.000 9999-12-31 00:00:00.000 1
iPhone 550.29 2014-01-13 07:15:00.000 2014-01-15 06:00:00.000 0
iPhone 490.68 2014-01-15 06:00:00.000 9999-12-31 00:00:00.000 1
January 30, 2014 at 11:08 pm
SELECT X.PRODUCTNAME, X.PRICE, X.STARTDATE, X.MAXENDDATE, X.FLAGACTIVEPRICE
FROM (
SELECT ProductName
, Price
, StartDate
, EndDate
, FlagActivePrice
, ROW_NUMBER() OVER ( PARTITION BY ProductName, Price order by ProductName) as rownum
, MAX(EndDate) OVER ( PARTITION BY ProductName, Price ) AS MaxEndDate
FROM @ProductHistory
) X
WHERE X.ROWNUM = 1
hope it helps
January 31, 2014 at 8:21 am
Thank you so much twin.devil. that's 99% correct. On that I want to keep track of flag for latest price. Mean if Enddate is '12/31/9999 0:00' then put FlagActive as 1 else 0.
Eg
Book 23.27 2014-01-15 07:12:00.000 9999-12-31 00:00:00.000 0 -- Instead of FlagActive 0, it need to be 1. Cause that is latest price.
similarly,
iPhone 490.68 2014-01-15 06:00:00.000 9999-12-31 00:00:00.000 0 --Instead of FlagActive 0, it need to be 1. Cause that is latest price.
where as ,
Laptop 405.29 2014-01-17 08:00:00.000 9999-12-31 00:00:00.000 1 -- it have only one records that's latest this is good.
I try to use case,
case when EndDate = '9999-12-31 00:00:00.000' then '1' else '0' end as FlagActivePrice but it is not working.
January 31, 2014 at 8:37 am
Try this:
SELECT X.PRODUCTNAME, X.PRICE, X.STARTDATE, X.MAXENDDATE, case when X.MaxEndDate = '9999-12-31 00:00:00.000' then 1 else 0 end as FlagActivePrice
FROM (
SELECT ProductName
, Price
, StartDate
, EndDate
, FlagActivePrice
, ROW_NUMBER() OVER ( PARTITION BY ProductName, Price order by ProductName) as rownum
, MAX(EndDate) OVER ( PARTITION BY ProductName, Price ) AS MaxEndDate
FROM ProductHistory
) X
WHERE X.ROWNUM = 1
January 31, 2014 at 9:02 am
Thank you Keith Tate,
That's exactly I was looking for. I just put my case statement in wrong place.
Thanks.
February 7, 2014 at 1:45 pm
drop table ProductHistory
CREATE TABLE ProductHistory
(
ProductName varchar(20),
Price float,
StartDate datetime,
EndDate datetime,
FlagActivePrice smallint
)
insert into ProductHistory values
('Pen',0,'01/15/2014 4:13','01/16/2014 6:15',0),
('Pen',0,'01/16/2014 6:15','12/31/9999 0:00',1),
('Book',22.57,'01/14/2014 3:15','01/15/2014 7:12',0),
('Book',23.27,'01/15/2014 7:12','01/17/2014 8:26',0),
('Book',23.27,'01/17/2014 8:26','12/31/9999 0:00',1),
('Laptop',405.29,'01/17/2014 8:00','12/31/9999 0:00',1),
('iPhone',550.29,'01/13/2014 7:15','01/14/2014 2:28',0),
('iPhone',550.29,'01/14/2014 2:28','01/15/2014 6:00',0),
('iPhone',490.68,'01/15/2014 6:00','01/17/2014 8:00',0),
('iPhone',490.68,'01/17/2014 8:00','01/18/2014 5:00',0),
('iPhone',490.68,'01/18/2014 5:00','01/21/2014 7:36',0),
('iPhone',490.68,'01/21/2014 7:36','01/23/2014 6:45',0)
('iPhone',550.29,'01/23/2014 6:45','12/31/9999 0:00',1) --I added one more records. since this records is later price change back to the price which once the iphone price was.
Expected output as following:
try to store history of price of product. but if price is same then those records need not pull. Eg. Pen's both price was 0. I do not need both records. but we have data of Pen's from '01/15/2014 4:13' so put that as startdate and end date is '12/31/9999 0:00' and this is flagactive should be 1. Where as in Book's second and third have same price so one need to delete but startdate need to update.
ProductName Price StartDate EndDate FlagActivePrice
Pen 0 2014-01-15 04:13:00.000 9999-12-31 00:00:00.000 1
Book 22.57 2014-01-14 03:15:00.000 2014-01-15 07:12:00.000 0
Book 23.27 2014-01-15 07:12:00.000 9999-12-31 00:00:00.000 1
Laptop 405.29 2014-01-17 08:00:00.000 9999-12-31 00:00:00.000 1
iPhone 550.29 2014-01-13 07:15:00.000 2014-01-15 06:00:00.000 0
iPhone 490.68 2014-01-15 06:00:00.000 2014-01-23 6:45:00.000 0
iPhone 550.29 2014-01-23 6:45:00.000 9999-12-31 00:00:00.000 1 --since this record should be display, compare to previous price it is different. But 550.29 was one of history price. How to hand this situation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply