Query (adjacent rows) -> Gantt chart

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)

Query (adjacent rows) -> Gantt chart

Author

Topic

Beer
Starting Member

7 Posts

Posted - 2009-06-19 : 09:10:47

Hello!


Table EventTypes
---------------------
ID | EventType      |
---------------------
1  |  Login	    |
2  |  Logout        |
3  |  Warning       |
4  |  .....         |


                                             +-------- this is actually integer FK linked to the users table. I wrote names to make it easy to understand
Table SysEvents                              |
---------------------------------------------------    ------------------------------------------
ID |      Timestamp      |  EventType_ID | UserID |    Description  <- not in the table (just here for additional info)
---------------------------------------------------    ------------------------------------------
1  |  19.6.2009 12:00:00 |       1       |  John  |    successfully logged in  
2  |  19.6.2009 12:30:00 |       1       |  Maria |    successfully logged in  
3  |  19.6.2009 14:00:00 |       2       |  John  |    is going home --> loged out
4  |  19.6.2009 15:00:00 |       3       |  Alex  |    tried to login with wrong password (attempt 1) (this line is not important, but it is in the table!)
5  |  19.6.2009 16:00:00 |       1       |  Alex  |    successfully logged in
6  |  19.6.2009 17:00:00 |       2       |  Maria |    is going home --> loged out
7  |  19.6.2009 17:00:00 |       2       |  Alex  |    is going home --> loged out
8  |  19.6.2009 18:00:00 |       1       |  John  |    successfully logged in  
9  |  19.6.2009 19:00:00 |       2       |  John  |    is going home --> loged out

I want to use gantt chart to show WHEN AND HOW LONG a selected user has been logged in.
Each user can login/logout more than once in one day.
More then one users can be logged in at the same time.

The query I want to do is to answer the questions:

1. I want to know (timestamp filter was set to return data for 19.6.2009 (1 day) )
a) When John logged-in, whenn he logged-out AND how long (duration)
b) I can select more than 1 user, so:
When were Maria, John, Alex,... loged in and how long.

I believe Gantt diagram is the right choice, but...

My Chart control (DevExpress or TeeChart) expects to have start AND stop time in the same row (record).

The result I expect

     Users
       |
       |
       |
John   |      |XXXXX  2h  XXXXXX|                                   |XX 1h XX|
       |
Alex   |                                          |XX 1h XX|         
       |
Maria  |          |XXXXXXXXXXXXXXX  4.5h  XXXXXXXXXXXXXXXXX|
       |
       +------|--------|--------|--------|--------|--------|--------|--------|----   Timestamp
             12        13       14       15       16       17       18       19

I have total control over the tables, I can modify them as I want.

btw: I tried to design table like this (just for login/logout):
ID | Timestamp_login | Timestamp_logout | UserID |

But then I have to track ID for each user until he logs-out.
What if the system crashes, and then all users re-login.
(I need to analyze table and find such anomalies)

I hope you understand what I want.

Thanks in advance.
Beer

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 11:52:24

I am sure here exists someone who knows a better solution

But it is an approach:

declare @sample table(Id int,[timestamp] datetime,Eventtype_id int,UserID varchar(255))
insert @sample
select 1,'2009-06-19 12:00',1,'John' union all
select 2,'2009-06-19 12:30',1,'Maria' union all
select 3,'2009-06-19 14:00',2,'John' union all
select 4,'2009-06-19 15:00',3,'Alex' union all
select 5,'2009-06-19 16:00',1,'Alex' union all
select 6,'2009-06-19 17:00',2,'Maria' union all
select 7,'2009-06-19 17:00',2,'Alex' union all
select 8,'2009-06-19 18:00',1,'John' union all
select 9,'2009-06-19 19:00',2,'John'

--select * from @sample

select 
loggedin.UserID,
loggedin.[timestamp] as loggedin,
loggedout.[timestamp] as loggedout,
DATEDIFF(MINUTE,loggedin.[timestamp],loggedout.[timestamp])/60.0 as Duration
from (select UserID,[timestamp],Eventtype_id, ROW_NUMBER() over (PARTITION by  UserID order by [timestamp],eventtype_id) as rownum from @sample) as loggedin
join (select UserID,[timestamp],Eventtype_id, ROW_NUMBER() over (PARTITION by  UserID order by [timestamp],eventtype_id) as rownum from @sample) as loggedout
  on loggedin.UserID = loggedout.UserID and
     loggedin.Eventtype_id = 1 and
     loggedout.Eventtype_id = 2 and
     loggedout.rownum = loggedin.rownum+1

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

Beer
Starting Member

7 Posts

Posted - 2009-06-19 : 16:23:29

I have 2 words to say: Thank you.
It works.

Although I don't have a clue what is this OVER & PARTITION BY clauses.

I think I understand the basic idea.
You create 2 tables from my existing table and add new column.
Then you just have to do join correctly. Link IDs and stuff.
Sounds easy, but I don't really understand the query.

However it works and that is just fine with me.
I will spend next few hours reading about OVER and PARTITION BY

What about the performance?
Let's say I have a table with 1-2 Million rows.
Statistic is generally filtered on daily, weekly or monthly basis,
but those 2 selects looks really nasty to me.

Thanks again,
Beer

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 17:43:48

About performance:
My two selects have no where clause.
You can do for each select:
select
UserID,
[timestamp],
Eventtype_id,
ROW_NUMBER() over (PARTITION by UserID order by [timestamp],eventtype_id) as rownum
from @sample
where [timestamp]>'20090618' and [timestamp]<'20090620'
to get only data for one day

For that stuff with "OVER & PARTITION BY clauses" read BOL for row_number().

Webfred

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

Beer
Starting Member

7 Posts

Posted - 2009-06-20 : 03:49:02

Thank you very much.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 20:14:41

Hmmm... what happens if someone punches in before midnight, works through midnight, and punches out after midnight?

What happens if someone like me does a 2 or 3 day death march to get some emergency code done?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:44:42

Using same sample data as Webfred, and until question by Jeff gets answered

SELECT		UserID,
		MAX(CASE WHEN recID % 2 = 0 THEN timestamp ELSE NULL END) AS [In],
		MAX(CASE WHEN recID % 2 = 1 THEN timestamp ELSE NULL END) AS [Out]
FROM		(
			SELECT	UserID,
				timestamp,
				Eventtype_id,
				ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY timestamp) - 1 AS recID
			FROM	@Sample
			WHERE	Eventtype_id IN (1, 2)
				AND timestamp >= @WantedDate
				AND timestamp < DATEADD(DAY, 1, @WantedDate)
		) AS s
GROUP BY	UserID,
		recID / 2
ORDER BY	UserID,
		recID / 2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources