only show last specific event - 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

General SQL Server Forums

New to SQL Server Programming

only show last specific event

Author

Topic

parabolam
Starting Member

4 Posts

Posted - 2009-07-23 : 08:46:04

hey all, i have a database which i query with SQL to show when a door is opened and closed. i want the query only to show if the door is currently open. the datatable shows data like the following:

FRIDGE TIME ACTION ALARM
FRIDGE3 27/06/2009 09:07 OPEN DOOR3
FRIDGE3 15/07/2009 15:40 CLOSED DOOR3
FRIDGE3 15/07/2009 15:40 OPEN DOOR4
FRIDGE3 15/07/2009 15:41 CLOSED DOOR4
FRIDGE1 24/06/2009 23:59 OPEN DOOR3
FRIDGE1 01/07/2009 00:31 OPEN DOOR3
FRIDGE1 03/07/2009 02:23 OPEN DOOR2
FRIDGE1 03/07/2009 02:24 CLOSED DOOR2
FRIDGE1 08/07/2009 05:41 OPEN DOOR3
FRIDGE1 17/07/2009 18:34 CLOSED DOOR3
FRIDGE2 04/07/2009 17:14 OPEN DOOR3
FRIDGE2 22/07/2009 01:03 CLOSED DOOR3
FRIDGE2 22/07/2009 01:48 OPEN DOOR3

What i want to show is if the last action was open and to show the door as open.. so the data thrown back should only be:

FRIDGE TIME ACTION ALARM
FRIDGE2 22/07/2009 01:48 OPEN DOOR3

Is this doable using SQL or how would i do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 08:50:23

select FRIDGE ,TIME ,ACTION ,ALARM from your_table as t1
where time=(select max(time) from your_table where action='OPEN')

Madhivanan

Failing to plan is Planning to fail

parabolam
Starting Member

4 Posts

Posted - 2009-07-24 : 06:36:41

im ot too sure where to put that code into my code. see my code below:

SELECT
FRIDGE AS FRIDGE
,TIME_OCCURED AS TIME
,ACTION AS ACTION
,ALARM AS ALARM
FROM
ALARM_HISTORY

WHERE

AND FRIDGE Like 'FRIDGE%'
AND ALARM Like '%OPEN%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 06:51:09

SELECT
FRIDGE AS FRIDGE
,TIME_OCCURED AS TIME
,ACTION AS ACTION
,ALARM AS ALARM
FROM
ALARM_HISTORY

WHERE

AND FRIDGE Like 'FRIDGE%'
AND ALARM Like '%OPEN%'
AND time=
(
SELECT max(time)
FROM
ALARM_HISTORY

WHERE

AND FRIDGE Like 'FRIDGE%'
AND ALARM Like '%OPEN%'
)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources