Another T-SQL puzzle

microsoft.public.sqlserver.programming

11 Feb 2006 4:58 AM

Alex

Hi all,

Sorry if this has been asked before, I've had a very long day and I think my
brain has switched off...

I have a table that records transactions (changes) in another table, and I'm
interested in getting an audit trail (i.e. who did what) for specific state
changes.

So, given the following.... (there are more columns, such as user id etc,
but these are irrelevant here)

CREATE TABLE [StateChanges] (
[m_id] [int] IDENTITY (1, 1) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL CONSTRAINT
[DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
[SensorID] [int] NOT NULL,
[State1] [varchar] (20) NOT NULL,
[State2] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO

INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')

If I'm looking for changes in State1 in each SensorID, then I'd want to get
the rows where State1 has changed from Pending to New, or from Pending to OK
etc. There are occasions where another state will change and State1 will
stay the same, in which case I only want the first occurence.

So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.

If anyone can help me here it would be greatly appreciated!!!

Cheers,
Alex

11 Feb 2006 5:59 AM

Louis Davidson

The key to this type of problem is a perfect sequence number. In your
example, for clarity I can cheat and use the identity value since you have
sorted the inserts by SensorIf and ChangeDateTime.

You just shift the data to check the previous value against the current
value. In SQL Server 2005, this is pretty easy to do

with orderedSet as
( --this set is used twice in the following query
select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
row_number() over (partition by sensorId order by changeDateTime) as
ordering
from    stateChanges
)
select *
from   orderedSet as s
   join orderedSet as s2
    on s.sensorId = s2.sensorId
     and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1

For 2000, you have to do the row number yourself, so it is more complex,
especially if your data set is far more complex.

select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
(select count(*)
   from   stateChanges as s2
   where s2.sensorId = stateChanges.sensorId
and s2.changeDateTime <= stateChanges.changeDateTime) as ordering
into #orderedSet
from    stateChanges

select *
from   #orderedSet as s
   join #orderedSet as s2
    on s.sensorId = s2.sensorId
     and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1

I use a temp table because it is just so much cleaner to deal with with the
two references since temp views are not allowed. Hope this help :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Alex" <nospam@hotmail.com> wrote in message
news:43ed6f20$0$5013$db0fefd9@news.zen.co.uk...
> Hi all,
>
> Sorry if this has been asked before, I've had a very long day and I think
> my brain has switched off...
>
> I have a table that records transactions (changes) in another table, and
> I'm interested in getting an audit trail (i.e. who did what) for specific
> state changes.
>
> So, given the following.... (there are more columns, such as user id etc,
> but these are irrelevant here)
>
> CREATE TABLE [StateChanges] (
> [m_id] [int] IDENTITY (1, 1) NOT NULL,
> [ChangeDateTime] [datetime] NOT NULL CONSTRAINT
> [DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
> [SensorID] [int] NOT NULL,
> [State1] [varchar] (20) NOT NULL,
> [State2] [varchar] (20) NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')
>
>
> If I'm looking for changes in State1 in each SensorID, then I'd want to
> get the rows where State1 has changed from Pending to New, or from Pending
> to OK etc. There are occasions where another state will change and State1
> will stay the same, in which case I only want the first occurence.
>
> So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.
>
> If anyone can help me here it would be greatly appreciated!!!
>
> Cheers,
> Alex
>