June 11, 2008 at 7:14 am
I would like to provide a dynamic column that has a value from a previous row, for example:
create table myTable (sales_person varchar(10), model varchar(10), date_sold datetime)
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-01' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-02' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-03' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-01' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-04' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-06' as datetime))
select row_number() over(partition by sales_person, model order by date_sold), * from myTable
drop table myTable
In this example, I would like to add an extra column dynamically called date_of_last sale which holds the value of date_sold from the previous row for each sales person. So row 2 would have an extra column that held 2008-01-01 for David.
Hope this makes sense!
June 11, 2008 at 7:24 am
HI,
TRy looking at the OVER() function in Books on line, this will put you in the write direction as it will give you a ROW number you can self join to. Similar to a running totals query I would think
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 11, 2008 at 7:45 am
; WITH Sales (Sales_Person, SaleID, Model, Date_Sold)
AS (
SELECT sales_person, Row_Number() OVER (Partition By Sales_Person, Model ORDER BY Date_Sold)
, Model, Date_Sold FROM MyTable
)
SELECT
S.*
, P.Date_Sold AS Prev_Date_Sold
FROM
Sales S
LEFT JOIN Sales P ON S.Sales_Person = P.Sales_Person AND S.SaleID = P.SaleID+1
June 11, 2008 at 7:53 am
Thanks Michael, that's what I was trying to achieve.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply