Time in state - 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

Time in state

Author

Topic

heson1
Starting Member

2 Posts

Posted - 2006-05-10 : 12:44:40

I've got a SQL that registers people passing in and out via their acess cards. Basically passing in means they set a discrete tag to 1 and out tag to 0. SQL logs delta so change is logged. I would now want to Query the database and just want the monthly time they've been in i.e. how long has the tag been 1.
I can pull a monthly list of the tags all statuses (1 and 0) and times but how do I put together a query that just gives me the time it been in 1 status?

following to pull a delta list i.e changes:

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(wk,-1,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 113), vValue, Quality, QualityDetail = v_History.QualityDetail, QualityString
FROM v_History
LEFT JOIN QualityMap ON QualityMap.QualityDetail = v_History.QualityDetail
WHERE TagName IN ('Pete_Smith')
AND vValue <= 1
AND wwVersion = 'Original'
AND wwRetrievalMode = 'Delta'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate

heson1

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-10 : 23:22:09

i know i am oversimplifying again...

where...
vValue=1...

--------------------
keeping it simple...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-11 : 00:12:33

He means the DURATION that they were in state 1.

I'll take a look at this tomorrow...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-11 : 01:33:45

oh like datediff of min(datetime) and max(datetime) inside
a group by tagname...?

--------------------
keeping it simple...

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-11 : 01:52:54

Are people going in and out more than once per day, and can they stay past midnight ?

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

heson1
Starting Member

2 Posts

Posted - 2006-05-11 : 04:28:44

It's a workplace so people can pass in and out several times and at any time - if access rules permit.
At the moment I'm using Excel to pull the list from SQL and then do calculations via Macros i.e. time difference from timestamp, from tag going to 1 and until back to O, then add all these times for the month.
Would like to pull it all via a SQLquery though.

heson1

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-05-11 : 11:04:37

I didn't understand your example -- don't see what all the columns are for, or even which one's got the tag status in it! Nonetheless, your question seems fairly straightforward.

Given a table structure like this:


CREATE TABLE EntranceLog (
  tag_id int NOT NULL,
  event_time datetime NOT NULL,
  direction varchar(3) NOT NULL CHECK ( direction IN ('in', 'out') ),
  PRIMARY KEY (tag_id, event_time)
)

You need to get, for each event, the next event (if any) for the same tag id. You can do this either with a subquery:


SELECT E1.tag_id, E1.event_time, E1.direction,
  E1.next_event_time, E2.direction AS next_direction
FROM (
    SELECT E1.tag_id, E1.event_time, E1.direction, (
        SELECT MIN(E2.event_time)
        FROM EntranceLog AS E2
        WHERE E1.tag_id = E2.tag_id
          AND E1.event_time < E2.event_time
      ) AS next_event_time
    FROM EntranceLog AS E1
  ) AS E1
LEFT JOIN EntranceLog AS E2
  ON E1.tag_id = E2.tag_id
 AND E1.next_event_time = E2.event_time
ORDER BY E1.tag_id, E1.event_time

or a join:


SELECT E1.tag_id, E1.event_time, E1.direction,
  E1.next_event_time, E2.direction AS next_direction
FROM (
    SELECT E1.tag_id, E1.event_time, E1.direction, MIN(E2.event_time) AS next_event_time
    FROM EntranceLog AS E1
    LEFT JOIN EntranceLog AS E2
      ON E1.tag_id = E2.tag_id
     AND E1.event_time < E2.event_time
    GROUP BY E1.tag_id, E1.event_time, E1.direction
  ) AS E1
LEFT JOIN EntranceLog AS E2
  ON E1.tag_id = E2.tag_id
 AND E1.next_event_time = E2.event_time
ORDER BY E1.tag_id, E1.event_time

depending on how many events per tag_id you have, one or the other may be better: certainly they tend to produce different execution plans.

If (tag_id, event_time) is not a candidate key for your event table, you'll need to decide for yourself what two events at the same time for the same tag id mean.

You'll have to decide what to do with 'in' events that have never been followed by 'out' events (maybe launch a search party!)

You may or may not have to worry about what two or more consecutive 'in' events or 'out' events on given tag id means.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources