June 22, 2010 at 10:38 am
Hi everyone,
I want data in historystart and historyend columns in my table dbo.pricehistory using editdate in the existing table dbo.pricebookhistory.
i am creating two tables one is dbo.pricebookhistory which has data and dbo.pricehistory .
dbo.pricebookhistory Example:
create table dbo.pricebookhistory(
pricebookHistoryID int,
editdate datetime,
opportunityid int,
month datetime,
qty int,
asp money
)
insert into dbo.pricebookhistory values (1,'2009-01-14 19:20:18.487',12231,'2009-04-01 00:00:00',2298,0.500)
insert into dbo.pricebookhistory values(2,'2009-01-15 08:05:38.917',12231,'2009-04-01 00:00:00',2367,0.500)
insert into dbo.pricebookhistory values(3,'2009-01-14 19:20:18.487',12231,'2009-05-01 00:00:00',7542,0.500)
insert into dbo.pricebookhistory values(4,'2009-01-15 08:05:38.933',12231,'2009-05-01 00:00:00',4366,0.500)
insert into dbo.pricebookhistory values(5,'2009-01-20 09:50:27.493',19908,'2009-05-01 00:00:00',2822,121.50)
insert into dbo.pricebookhistory values(6,'2009-02-12 13:25:34.913',19908,'2009-05-01 00:00:00',233433,121.50)
insert into dbo.pricebookhistory values(7,'2009-04-13 23:27:41.543',19908,'2009-05-01 00:00:00',23566,131.50)
now when i select for opportunityid 12231 and month 2009-04-01
select * from dbo.pricebookhistory where opportunityid=12231 and month='2009-04-01'
i will get this:
pricebookHistoryIDeditdate opportunityid month qtyasp
1 2009-01-14 19:20:18.487 12231 2009-04-01 00:00:00.000 22980.50
2 2009-01-15 08:05:38.917 12231 2009-04-01 00:00:00.00023670.50
now i am going to create a table with historystart and historyend columns in another database:
dbo.pricehistory example:
create table dbo.pricehistory
(
pricehistory int identity(1,1)
historystart datetime,
historyend datetime,
pricebookHistoryID int,
editdate datetime,
opportunityid int,
month datetime,
qty int,
asp money
)
now i need the data for every opportunityid and month like this:
in this case i am taking opportunityid=12231 and month='2009-04-01',i need all the rows and columns above plus these two columns in dbo.pricehistory table:
historystart
2009-01-14 19:20:18.487
2009-01-15 08:05:38.917
historyend
2009-01-15 08:05:38
Null
I worte them in separate lines but ,the first row in historystart column and histroyend fall in the same row and the second row in historystart and historyend column fall in the same row,if i put them together it is confusing thats is why i wrote them in separate lines.the remaining columns will be the same as dbo.pricebookhistory .
Can anyone help me with this :
June 22, 2010 at 10:48 am
Would you please clarify the content of dbo.pricehistory as well as your expected result?
June 22, 2010 at 11:22 am
I have attached the note pad with expected results.
sorry for the confusion.
June 22, 2010 at 12:18 pm
Is the following code close to what you're looking for?
I'm using a self referencing join based on ROW_NUMBER.
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY opportunityid,[MONTH] ORDER BY pricebookHistoryID ) AS ROW
FROM pricebookhistory
)
SELECT
cte1.editdate AS historystart,
cte2.editdate AS historyend,
cte1.*
FROM cte cte1
LEFT OUTER JOIN cte cte2
ON cte1.opportunityid=cte2.opportunityid
AND cte1.[month]=cte2.[month]
AND cte1.row =cte2.row - 1
June 22, 2010 at 12:37 pm
This is what i am looking for .
Excellent .Thank you very much.
June 22, 2010 at 2:50 pm
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply