How to determine the pervious record based on the

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to determine the pervious record based on the

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-12 : 21:14:30

Hello All,

I want to know the previous record based on the current record field; the problem is that the prevoius record is not always 1 month before the current record. Below is my attempt on determining the previous record by using the
dateadd(mm,-1,process_date), this is not correct does anyone know how to determine the pervious record?

select process_date, dateadd(mm,-1,process_date) as prev_process_date from Test_Table

--Create table for testing
CREATE TABLE [dbo].[Test_Table](
[Process_date] [datetime] NULL
) ON [PRIMARY]

--Insert test samples
insert into Test_Table Values ('2007-09-30 00:00:00.000')
insert into Test_Table Values ('2007-10-31 00:00:00.000')
insert into Test_Table Values ('2008-07-31 00:00:00.000')
insert into Test_Table Values ('2008-12-31 00:00:00.000')
insert into Test_Table Values ('2009-02-28 00:00:00.000')
insert into Test_Table Values ('2009-03-31 00:00:00.000')
insert into Test_Table Values ('2009-06-30 00:00:00.000')
insert into Test_Table Values ('2007-12-31 00:00:00.000')
insert into Test_Table Values ('2008-03-31 00:00:00.000')
insert into Test_Table Values ('2008-04-30 00:00:00.000')
insert into Test_Table Values ('2009-01-31 00:00:00.000')
insert into Test_Table Values ('2009-04-30 00:00:00.000')
insert into Test_Table Values ('2009-05-31 00:00:00.000')
insert into Test_Table Values ('2009-07-31 00:00:00.000')
insert into Test_Table Values ('2009-08-31 00:00:00.000')
insert into Test_Table Values ('2009-09-30 00:00:00.000')

Please advice.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-12 : 21:30:45

make use of row_number()

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-12 : 22:24:25

Thanks for the reply Khtan, but I tried the row_number() function, eventhrou it seems like ti sorts the date field, how would I insert date values in the prev_process_date instead on a number?

Below is what I wrote..

SELECT process_date, ROW_NUMBER() OVER(ORDER BY process_date DESC) AS 'prev_process_date'
FROM test_table
order by process_date

Please advice. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-12 : 22:34:46

[code]
; with cte
as
(
SELECT process_date, ROW_NUMBER() OVER(ORDER BY process_date DESC) AS row_no
FROM test_table
~~order by process_date~~
)
select c.*, p.process_date as [prev_process_date]
from cte c
left join cte p on c.row_no = p.row_no + 1
[/code]

EDIT : removed order by in inner query. Thanks sanoj

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-12 : 23:43:15

Khtan,

When I executed the your query below I recieved the following error:

Msg 1033, Level 15, State 1, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Please advice. Thanks.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-12 : 23:51:59

--Remove the order by from inline view
with cte
as
(
SELECT process_date, ROW_NUMBER() OVER(ORDER BY process_date DESC) AS row_no
FROM test_table
)
select c.*, p.process_date as [prev_process_date]
from cte c
left join cte p on c.row_no = p.row_no + 1

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-13 : 01:38:56

Thanks folks it works now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources