SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Best way to retrieve previous date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
204 Posts

Posted - 11/01/2012 :  05:42:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/01/2012 :  06:44:44  Show Profile  Reply with Quote
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
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
204 Posts

Posted - 11/01/2012 :  07:07:18  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/01/2012 :  07:56:51  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/01/2012 :  09:11:05  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000