Complex group by? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Complex group by?

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 09:06:44

Hi friends,

I got a question and simplified it for you:
I need to get from this table:

id value
1 0
2 1
3 1
4 0
5 0
6 0
7 1
8 0
9 0
10 0
11 0
12 1
13 1
14 1
15 1
16 0

To this Query:

2 1
7 1
12 1

The id column is in reality a timestamp column and the Value column is a proximity switch.
The 'switching' time can differ and the time between switches can differ too. How?

Please help,

Greetings Joris

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-21 : 09:31:40

[code]declare @sample table (xid int, xvalue int)
insert @sample
select 1, 0 union all
select 2, 1 union all
select 3, 1 union all
select 4, 0 union all
select 5, 0 union all
select 6, 0 union all
select 7, 1 union all
select 8, 0 union all
select 9, 0 union all
select 10, 0 union all
select 11, 0 union all
select 12, 1 union all
select 13, 1 union all
select 14, 1 union all
select 15, 1 union all
select 16, 0

--select * from @sample

select s1.xid, s1.xvalue
from @sample as s1
join @sample as s2
on s2.xid = s1.xid-1 and
s1.xvalue = 1 and
s2.xvalue = 0
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 09:42:12

Yes Thank you only the next problem is that in reality id is not id but it is a random timestamp...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-21 : 09:51:19

Can you show example data?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-21 : 09:52:16

See http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

E 12°55'05.63"
N 56°04'39.26"

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 10:03:01

I got it thank you very much, the time stamp has sampling interval of near 0.2 seconds

this is my code

select cast(s1.thetime as datetime), s1.opbrengst
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.opbrengst = 1 and
s2. opbrengst = 0

thanks all

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-21 : 10:27:03

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-27 : 12:38:45

Well it was working until today I want to add the water sensor.
I made a screenshot because I do not understand what is wrong,
normally it should log every 0 to 1 flansh, but as you can see sometimes it doesnt: