I have to capture ID after checking two consecutive rows are non zero for a given masterID then capture first that first ID and date time associated with it.
can some one help? my table looks like below. it displays valye based on select * from table1 where masterid = '12345'
what i want in my final result is "11/25/2012 10:32PM 14 12345 16". basically looking for to find a MAX(id) after comparing two row.
with CTE as
(
select
*,
ROW_NUMBER() OVER(ORDER BY DateTimeCol, Id) as RowNum
from Table1
where masterid = '12345'
)
select top 1 f.*
from CTE as f
join CTE as s
on s.RowNum = f.RowNum + 1 and s.NonZeroColumn <> 0
where f.NonZeroColumn <> 0
with CTE as
(
select
*,
ROW_NUMBER() OVER(ORDER BY Date_Time, Id) as RowNum
from dbo.minavl
where masterid = '12345'
)
select top 1 f.*
from CTE as f
join CTE as s
on s.RowNum = f.RowNum + 1 and s.Val > 0
where f.Val > 0
But do not use a View (not required) or put the entire script as I posted in the View.
Arial12
Member
29 Points
160 Posts
Compare two ROW in same Table: checking two consecutive non zero value
Nov 26, 2012 10:27 PM|LINK
Hi all,
I have to capture ID after checking two consecutive rows are non zero for a given masterID then capture first that first ID and date time associated with it.
can some one help? my table looks like below. it displays valye based on select * from table1 where masterid = '12345'
what i want in my final result is "11/25/2012 10:32PM 14 12345 16". basically looking for to find a MAX(id) after comparing two row.
spapim
Contributor
2448 Points
369 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 27, 2012 03:02 AM|LINK
Hi,
Try something like this:
with CTE as ( select *, ROW_NUMBER() OVER(ORDER BY DateTimeCol, Id) as RowNum from Table1 where masterid = '12345' ) select top 1 f.* from CTE as f join CTE as s on s.RowNum = f.RowNum + 1 and s.NonZeroColumn <> 0 where f.NonZeroColumn <> 0Hope this helps.
www.imobiliariasemsuzano.com.br
Arial12
Member
29 Points
160 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 27, 2012 03:49 AM|LINK
Thanks but I need is to compare first two rows where val is non zero and out two display first row.
for example, row1.val > 0 and row2.val >0 then don't really care anything after and display result with row1 data.
spapim
Contributor
2448 Points
369 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 27, 2012 11:35 AM|LINK
Try a test with the script below:
declare @Table1 table (DateTimeCol datetime, Id int, Val int); insert into @Table1 values ('11/25/2012 10:25 PM', 1, 5), ('11/25/2012 10:25 PM', 2, 0), ('11/25/2012 10:26 PM', 3, 0), ('11/25/2012 10:26 PM', 4, 0), ('11/25/2012 10:26 PM', 5, 1), ('11/25/2012 10:27 PM', 6, 0), ('11/25/2012 10:27 PM', 7, 0), ('11/25/2012 10:28 PM', 8, 0), ('11/25/2012 10:29 PM', 9, 0), ('11/25/2012 10:29 PM', 10, 0), ('11/25/2012 10:30 PM', 11, 0), ('11/25/2012 10:30 PM', 12, 0), ('11/25/2012 10:31 PM', 13, 0), ('11/25/2012 10:31 PM', 14, 0), ('11/25/2012 10:31 PM', 15, 0), ('11/25/2012 10:32 PM', 16, 14), ('11/25/2012 10:32 PM', 17, 3); with CTE as ( select *, ROW_NUMBER() OVER(ORDER BY DateTimeCol, Id) as RowNum from @Table1 --where masterid = '12345' ) select top 1 f.* from CTE as f join CTE as s on s.RowNum = f.RowNum + 1 and s.Val > 0 where f.Val > 0Hope it's useful.
www.imobiliariasemsuzano.com.br
Arial12
Member
29 Points
160 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 27, 2012 11:19 PM|LINK
Thanks spapim. I want to create a SQL View
create view dbo.vw_1
as
with CTE as ( select *, ROW_NUMBER() OVER(ORDER BY DateTimeCol, Id) as RowNum from @Table1 )
select f.id,min(datetime) from
CTE as f join CTE as s on s.RowNum = f.RowNum + 1 and s.Val > 0
where f.Val > 0
group by f.id
and this gives me an error message and view does not get created. any idea why? or how?
spapim
Contributor
2448 Points
369 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 28, 2012 09:26 AM|LINK
Hi:
Try to adapt DateTime column name and table name on the line below:
If the script does not work, post the error message.
Hope this helps.
www.imobiliariasemsuzano.com.br
Arial12
Member
29 Points
160 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 28, 2012 04:20 PM|LINK
that is what I had done but then after when i run this query results differ than running this together.
select top 1 f.*
from CTE as f
join CTE as s
on s.RowNum = f.RowNum + 1 and s.Val > 0
where f.Val > 0
with CTE as
( select
*,
ROW_NUMBER() OVER(ORDER BY DateTimeCol, Id) as RowNum
from @Table1
masterid = '12345'
)
select top 1 f.*
from CTE as f
join CTE as s
on s.RowNum = f.RowNum + 1 and s.Val > 0
where f.Val > 0 and
masterid = '12345'
consider this data:
2012-11-27 03:31:32.000 0 NULL
2012-11-27 03:32:12.000 12 269
2012-11-27 03:32:33.000 3 NULL
2012-11-27 03:32:53.000 0 NULL
2012-11-27 03:33:21.000 6 271
2012-11-27 03:33:30.000 14 266
2012-11-27 03:33:58.000 22 180
2012-11-27 03:34:00.000 8 179
2012-11-27 03:34:20.000 11 101
2012-11-27 03:35:20.000 0 NULL
2012-11-27 03:35:50.000 0 NULL
2012-11-27 03:36:21.000 0 NULL
2012-11-27 03:36:50.000 0 NULL
2012-11-27 03:37:20.000 0 NULL
2012-11-27 03:37:51.000 0 NULL
2012-11-27 03:38:20.000 0 NULL
2012-11-27 03:38:40.000 9 NULL
2012-11-27 03:39:00.000 3 NULL
if run together result I get is this:
12
6
14
22
8
9
after creating view on row_number running a second part give
6
14
22
i am not able to find out why?
spapim
Contributor
2448 Points
369 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 28, 2012 04:35 PM|LINK
What is the name of your table?
What is the name of the column that stores the date and time?
How is the complete code of your View?
www.imobiliariasemsuzano.com.br
Arial12
Member
29 Points
160 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 28, 2012 04:48 PM|LINK
I create a view
create view dbo.vw_1
as
select *, ROW_NUMBER() OVER(ORDER BY [Date_Time], Id) as RowNum from dbo.minavl
(dbo,minavl has so many row with other masterids)
then I run,
select f.*,min(date_time)
from dbo.vw_1 as f
join dbo.vw_1 as s
on s.RowNum = f.RowNum + 1 and s.Val > 0
where f.Val > 0 and
masterid = '12345'
groupby f.id,f.val,f.date_time
what I actually want is to get a first date_time where two consecutive rows have val >0
so, last dataset i have i should get 2012-11-27 03:32:12.000 (since "12" is first having following row val non zero ) instead i get
this 2012-11-27 03:33:21.000 (" 6 ") time associated with "6"
spapim
Contributor
2448 Points
369 Posts
Re: Compare two ROW in same Table: checking two consecutive non zero value
Nov 28, 2012 05:12 PM|LINK
Try the script below:
with CTE as ( select *, ROW_NUMBER() OVER(ORDER BY Date_Time, Id) as RowNum from dbo.minavl where masterid = '12345' ) select top 1 f.* from CTE as f join CTE as s on s.RowNum = f.RowNum + 1 and s.Val > 0 where f.Val > 0But do not use a View (not required) or put the entire script as I posted in the View.
www.imobiliariasemsuzano.com.br