October 13, 2008 at 6:01 am
I want to solve this problem if possible by a select and not with a cursor
I have the following code
declare @TestTable table
(
startdate datetime,
testgroup int,
testvalue int
);
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L2)
insert into @TestTable ( startdate,testgroup,testvalue)
SELECT
DATEADD(minute,(n-1)*15 ,getdate() ),
1,
case
when SIN(n /3) > 0 then 1
else
0
end
from num ;
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L2)
insert into @TestTable ( startdate,testgroup,testvalue)
SELECT
DATEADD(minute,(n-1)*15 ,getdate() ),
2,
case
when SIN(n /2) > 0 then 1
else
0
end
from num ;
select
startdate,
testgroup,
testvalue,
ROW_NUMBER ( ) over (partition by testgroup order by startdate) as row_id
from
@TestTable
I want to change the select with the following contion
order by the field testgroup
give me the row_id 's
where the next 4 row_columns have a testvalue of 1
Example:
startdate testgroup testvalue row_id
2008-10-13 14:03:43.190102
2008-10-13 14:18:43.190113
2008-10-13 14:33:43.190114
2008-10-13 14:48:43.190115
2008-10-13 15:03:43.190116
2008-10-13 15:18:43.190117
2008-10-13 15:33:43.190118
2008-10-13 15:48:43.190119
2008-10-13 16:03:43.1901110
2008-10-13 16:18:43.1901111
2008-10-13 16:33:43.1901012
2008-10-13 16:48:43.1901013
I only want the following records from the select
startdate testgroup testvalue row_id
2008-10-13 14:18:43.190113
2008-10-13 14:33:43.190114
2008-10-13 14:48:43.190115
2008-10-13 15:03:43.190116
2008-10-13 15:18:43.190117
2008-10-13 15:33:43.190118
October 13, 2008 at 6:22 am
Here is an option - you could just join back to the table three times:
[font="Courier New"]declare @TestTable table
(
startdate datetime,
testgroup int,
testvalue int
);
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L2)
insert into @TestTable ( startdate,testgroup,testvalue)
SELECT
DATEADD(minute,(n-1)*15 ,getdate() ),
1,
case
when SIN(n /3) > 0 then 1
else
0
end
from num ;
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L2)
insert into @TestTable ( startdate,testgroup,testvalue)
SELECT
DATEADD(minute,(n-1)*15 ,getdate() ),
2,
case
when SIN(n /2) > 0 then 1
else
0
end
from num ;
select
startdate,
testgroup,
testvalue,
ROW_NUMBER ( ) over (partition by testgroup order by startdate) as row_id
from
@TestTable
; WITH DataTable (StartDate, TestGroup, TestValue, RowID) AS
(
select
startdate,
testgroup,
testvalue,
ROW_NUMBER ( ) over (partition by testgroup order by startdate) as row_id
from
@TestTable
)
SELECT
DT1.*
FROM
DataTable DT1
INNER JOIN DataTable DT2 ON DT1.TestGroup = DT2.TestGroup AND DT1.TestValue = DT2.TestValue AND DT1.RowID = DT2.RowID-1
INNER JOIN DataTable DT3 ON DT1.TestGroup = DT3.TestGroup AND DT1.TestValue = DT3.TestValue AND DT1.RowID = DT3.RowID-2
INNER JOIN DataTable DT4 ON DT1.TestGroup = DT4.TestGroup AND DT1.TestValue = DT4.TestValue AND DT1.RowID = DT4.RowID-3
WHERE
DT1.TestValue = 1[/font]
October 13, 2008 at 6:36 am
Thank you for the solution.
October 13, 2008 at 6:45 am
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply