with
CTE_L as
(
select
t2.*,
ROW_NUMBER() OVER(PARTITION BY mid, vh ORDER BY date_time DESC) as RowNum
from Table2 as t2
join Table1 as t1
on t1.mid = t2.mid and
t1.vh = t2.vh and
t1.date_time > t2.date_time
),
CTE_G as
(
select
t2.*,
ROW_NUMBER() OVER(PARTITION BY mid, vh ORDER BY date_time) as RowNum
from Table2 as t2
join Table1 as t1
on t1.mid = t2.mid and
t1.vh = t2.vh and
t1.date_time < t2.date_time
)
select mid, id, vh, date_time
from CTE_L
where RowNum <= 10
union all
select mid, id, vh, date_time
from CTE_G
where RowNum <= 10
order by mid, vh, date_time
Arial12
Member
29 Points
160 Posts
Find previous n and next n records of a given time
Feb 05, 2013 10:24 PM|LINK
Hi,
I have two tables. Table1 has threshold time based on this time I want records from table2.
like top 10 records where table2.time < table1.time and
top 10 records where table2.time > table1.time.
I can run this query individually but i want do it automatically.
tabl1 struture
mid vh datetime
1 e1 2/1/2013 20:33:59
1 t1 2/1/2013 20:14:52
2 t2 2/1/2013 19:11;34
3 e3 2/1/2013 16:13:55
3 t4 2/1/2013 16:00:00
Table2
mid id vh date_time
1 1 e1 2/1/2013 19:33:59
1 2 e1 2/1/2013 19:12:59
1 3 e1 2/1/2013 18:33:59
1 4 e1 2/1/2013 20:33:59
1 5 e1 2/1/2013 20:37:59
1 6 e1 2/1/2013 20:45:59
1 7 t1 2/1/2013 17:14:52
1 8 t1 2/1/2013 17:34:52
1 9 t1 2/1/2013 18:24:52
1 10 t1 2/1/2013 20:14:52
1 11 t1 2/1/2013 20:55:52
1 12 t1 2/1/2013 21:14:52
2 13 t2 2/1/2013 19:11;34
3 14 e3 2/1/2013 16:13:55
3 15 t4 2/1/2013 16:00:00 ...so on
I want result like this,
1 1 e1 2/1/2013 19:33:59
1 2 e1 2/1/2013 19:12:59
1 3 e1 2/1/2013 18:33:59
1 5 e1 2/1/2013 20:37:59
1 6 e1 2/1/2013 20:45:59
1 7 t1 2/1/2013 17:14:52
1 8 t1 2/1/2013 17:34:52
1 9 t1 2/1/2013 18:24:52
1 11 t1 2/1/2013 20:55:52
1 12 t1 2/1/2013 21:14:52
samething for t2,e2,t3 ,e4 ect.
can someone help please?
imobsuz
Participant
1278 Points
195 Posts
Re: Find previous n and next n records of a given time
Feb 05, 2013 11:06 PM|LINK
HI,
Try:
with CTE_L as ( select t2.*, ROW_NUMBER() OVER(PARTITION BY mid, vh ORDER BY date_time DESC) as RowNum from Table2 as t2 join Table1 as t1 on t1.mid = t2.mid and t1.vh = t2.vh and t1.date_time > t2.date_time ), CTE_G as ( select t2.*, ROW_NUMBER() OVER(PARTITION BY mid, vh ORDER BY date_time) as RowNum from Table2 as t2 join Table1 as t1 on t1.mid = t2.mid and t1.vh = t2.vh and t1.date_time < t2.date_time ) select mid, id, vh, date_time from CTE_L where RowNum <= 10 union all select mid, id, vh, date_time from CTE_G where RowNum <= 10 order by mid, vh, date_timeHope this helps.
Arial12
Member
29 Points
160 Posts
Re: Find previous n and next n records of a given time
Feb 06, 2013 08:24 PM|LINK
Thanks Imobsuz. I would like to create a view out of this so that it aumatically calculates the result.
i am getting error because of "With"
can you help or suggest?
Thanks again,
imobsuz
Participant
1278 Points
195 Posts
Re: Find previous n and next n records of a given time
Feb 06, 2013 09:29 PM|LINK
Try:
If it does not work that way is better you post the error message.
Hope this help.