query active and inactive event table

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)

query active and inactive event table

Author

Topic

kwangba
Starting Member

7 Posts

Posted - 2012-01-17 : 16:35:51

i have table that have column like
eventtime parameter001 value001 parameter002 value002 parameter003 value003 parameter004 value004 and so on to parameter100 value100

this is partial of my table
eventtime     parameter001     value001     parameter002     value002     parameter003     value003
2012-01-11 00:01:00.0000000     a     0     b     0     c     0
2012-01-11 00:02:00.0000000     a     1     b     0     c     1
2012-01-11 00:03:00.0000000     a     1     b     1     c     1
2012-01-11 00:04:00.0000000     a     1     b     1     c     1
2012-01-11 00:05:00.0000000     a     0     b     0     c     0
2012-01-11 00:06:00.0000000     a     1     b     1     c     1
2012-01-11 00:07:00.0000000     a     1     b     1     c     1
2012-01-11 00:08:00.0000000     a     0     b     0     c     0
2012-01-11 00:09:00.0000000     a     0     b     0     c     0

the result that i want is
eventtime     parameter     value
2012-01-11 00:02:00.0000000     a     1
2012-01-11 00:05:00.0000000     a     0
2012-01-11 00:06:00.0000000     a     1
2012-01-11 00:08:00.0000000     a     0
2012-01-11 00:03:00.0000000     b     1
2012-01-11 00:05:00.0000000     b     0
2012-01-11 00:06:00.0000000     b     1
2012-01-11 00:08:00.0000000     b     0
2012-01-11 00:02:00.0000000     c     1
2012-01-11 00:05:00.0000000     c     0
2012-01-11 00:06:00.0000000     c     1
2012-01-11 00:08:00.0000000     c     0

the code that i try but not even close


select * from
(select
row_number() over(partition by value001 order by eventtime ) as f,
row_number() over(partition by value001 order by eventtime desc) as l,*
from events) e
where (f = 1 or l = 1) and value001 = 1
order by eventtime

really need help!! T^T
- how do i get the first for each row = 1 of parameter (just can get the first of all)
- how do i get the first for each row = 0 only after row = 1 (really can figure out)
- how do i grap the columns from the right like parameter002 value002 parameter090 value090 into the result(really can figure out)

really thank for any advise and sorry for my bad language

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-17 : 18:53:10

[CODE];with cte
as (
select *, row_number() over(order by EventTime) rn
from events
)
select b.EventTime, b.parameter001, b.value001
from cte a
inner join
cte b
on a.rn + 1 = b.rn
and a.value001 <> b.value001

union all

select b.EventTime, b.parameter002, b.value002
from cte a
inner join
cte b
on a.rn + 1 = b.rn
and a.value002 <> b.value002

union all

select b.EventTime, b.parameter003, b.value003
from cte a
inner join
cte b
on a.rn + 1 = b.rn
and a.value003 <> b.value003[/CODE]HTH

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

kwangba
Starting Member

7 Posts

Posted - 2012-01-18 : 00:35:28

very very thank you that is what i want
i have some few question
i have to repeatedly add this part of query and change column name to parameterxxx right?

select b.EventTime, b.parameterxxx, b.valuexxx
from cte a
inner join
    cte b
        on  a.rn + 1 = b.rn
        and a.valuexxx <> b.valuexxx
union all

what does it mean on this part of query?

on  a.rn + 1 = b.rn
        and a.value001 <> b.value001

and if i want to know the range between active and inactive what should i do i try to add datediff but it not seem to work


select b.EventTime,b.parameter001, b.value001 ,datediff(mi,a.EventTime,b.EventTime)
from cte a
inner join
    cte b
        on  a.rn + 1 = b.rn
        and a.value001 <> b.value001

by the way really really thank you for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-18 : 11:56:14

The original cte is designed to create a row number (rn) on ordered data. Once we have a row number, the logic of "a.rn + 1 = b.rn" is used to join consecutive rows (e.g., Row 1 joins to Row 2, Row 2 joins to row 3, etc.). The "a.valueXXX <> b.valueXXX" is looking for changes in the value; Active or Inactive. You are right in thinking that we need to repeat the logic for each parameter/value pair and that the column names need to change for each repetition. Your DateDiff logic is finding the time between when it was last Inactive and when it went Active or vice versa. Are you trying to determine the length of time that it stayed one value?

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

kwangba
Starting Member

7 Posts

Posted - 2012-01-19 : 01:34:09

thank you.
i have an additional column the table change to be

additional column
eventtime parameter001 value001 quality001
2012-01-11 00:05:00.0000000 a 0 good
2012-01-11 00:06:00.0000000 a 1 good
2012-01-11 00:07:00.0000000 a 1 good
2012-01-11 00:08:00.0000000 a 0 bad
2012-01-11 00:09:00.0000000 a 0 good

the result
eventtime parameter value
2012-01-11 00:06:00.0000000 a 1
2012-01-11 00:09:00.0000000 a 0

i try

;with cte
as (
select *, row_number() over(order by EventTime) rn
from events where quality001 <> 'BAD'
)

it work fine
but i need to keep the "BAD Value" in cte query. How can i make condition to query only "GOOD" in the second query( the query that query from cte)

about datediff
i test this


select DATEDIFF(mi,a.eventtime,b.eventtime) tRange
from (select ROW_NUMBER() over(order by eventtime) rr,* from tt where value001 = 1) a
inner join 
(select ROW_NUMBER() over(order by eventtime) rr, * from tt where value001 = 0) b
on a.rr = b.rr

this query is correct or not? if i want range from the pair of active and inactive.
tt table is that table dump the second query to it(for test query).
my question is how can i reference to the last query(the query from cte).
how to wrap all this part of query

select b.EventTime, b.parameter001, b.value001
from cte a
inner join
    cte b
        on  a.rn + 1 = b.rn
        and a.value001 <> b.value001

union all

select b.EventTime, b.parameter002, b.value002
from cte a
inner join
    cte b
        on  a.rn + 1 = b.rn
        and a.value002 <> b.value002

union all

select b.EventTime, b.parameter003, b.value003
from cte a
inner join
    cte b
        on  a.rn + 1 = b.rn
        and a.value003 <> b.value003

and make a reference to the next query
thank you for any advice again and really thank for Bustaz Kool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources