Best way to retrieve previous date

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 2008 Forums

Transact-SQL (2008)

Best way to retrieve previous date

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2012-11-01 : 05:42:04

Hi there,

I want to return specific rows within a date range from a table using parameter.

So for example:
__________________________


declare  @tblInfo table(name varchar(50), PeriodStart datetime, Periodend datetime, value int)

insert into @tblInfo (name, PeriodStart,Periodend,value)
values 
('J','1 jan 2012','31 jan 2012',1),
('J','1 feb 2012','29 feb 2012',2),
('J','1 mar 2012','31 mar 2012',3),
('J','1 may 2012','31 may 2012',4),
('J','1 jun 2012','30 jun 2012',5),
('A','1 jan 2012','31 jan 2012',1),
('A','1 feb 2012','29 feb 2012',2),
('A','1 mar 2012','31 mar 2012',3),
('A','1 may 2012','31 may 2012',4),
('A','1 jun 2012','30 jun 2012',5)


declare @period datetime
set @period = '2012-05-01'

select name, value, periodstart,periodend
from @tblInfo
where @Period between periodstart and periodend

Which returns:


name	value	periodstart	periodend
J	4	2012-05-01 	2012-05-31 
A	4	2012-05-01 	2012-05-31

I also want to return the previous periodstart, PreviousEnd and value for each 'Name' as well, like so:


name	value	periodstart	periodend	Previousvalue	    PreviousPeriodstart	PreviousPeriodend
J	4	2012-05-01 	2012-05-31      3	      2012-03-01 	2012-03-31 	 
A	4	2012-05-01 	2012-05-31      3	      2012-03-01 	2012-03-31

The last period is not always last month, it could be 6 months or more since the last period.

I understand there are many ways to do this but what would be the best way in your opinion.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 06:44:44

Join the table to itself like below:

select a.name, a.value, a.periodstart,a.periodend,
	b.value, b.periodstart, b.Periodend
from @tblInfo a
LEFT JOIN @tblInfo b ON a.value = b.value + 1 AND a.name= b.name
where @Period between a.periodstart and a.periodend

cidr
Posting Yak Master

207 Posts

Posted - 2012-11-01 : 07:07:18

Thanks for replying sunitabeck

quote:

sunitabeck 01/11/2012
Join the table to itself like below:

That's close. Only, I can't use value field as this is random.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 07:56:51

What can you use to order the rows?

Because the rows in a table are by definition unordered, you need a deterministic way of specifying the order for finding the previous row.

If there is no ordering scheme, then, if you decided that any random ordering will suffice, that can be done as well.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-01 : 09:11:05

I'm just assuming the periods are orderable for this case.

select tbl.name, tbl.value, tbl.periodstart,tbl.periodend,prev.*
from @tblInfo tbl

CROSS APPLY (select top 1 ti.value,ti.PeriodStart,ti.Periodend
from @tblInfo ti
where tbl.name = ti.name
and tbl.PeriodStart > ti.PeriodStart

order by ti.PeriodStart desc
) prev

where @Period between tbl.periodstart and tbl.periodend

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources