Hey forum. Not a SQL newbie here but new to the forum. A somewhat advanced query has my stumped. I have a table of session information that provides log-ons and log-off times like this (column names are whenlogged, userid, msg): -
10/23/2010-8:21:46-AM 10 mimmy (10) started session
-
10/23/2010-8:07:21-AM 12 mark (12) started session
-
10/22/2010-2:38:37-PM 12 mark (12) started session
-
10/22/2010-2:27:21-PM 32 grace (32) ended session
-
10/22/2010-2:11:01-PM 32 grace (32) started session
-
I need to generate a report showing the session log-ons and log-offs, ordered by desc log-on date/time, something like this: -
userid session start session end
-
10 10/23/2010-8:21:46-AM ?
-
12 10/23/2010-8:07:21-AM ?
-
12 10/23/2010-2:38:37-PM ?
-
32 10/23/2010-2:11:01-PM 10/22/2010-2:27:21-PM
-
I am stuck at this left-join: -
select starts.userid,master.accountfullname(starts.userid ) as "user",starts.whenlogged as started, stops.whenlogged as stopped
-
from master.sessionlog starts left join master.sessionlog stops
-
on starts.userid=stops.userid and starts.whenlogged<stops.whenlogged
-
where starts.msgtype=0 and starts.userid<>0 and starts.msg like '% started session'
-
and stops.msgtype=0 and stops.userid<>0 and stops.msg like '% ended session'
-
order by starts.whenlogged desc
-
...since I cannot figure how to group the users in such a way as to take the closest log-off time after the log-on time for each user. The query should also gracefully handle the many cases where a user does not properly end the session (shown as ? in a report).
Can any SQL guru out there help?
Thanks,
Mark
1 1604
First, create your sample data..... -
-
drop table session;
-
-
with test_data
-
as
-
(
-
select cast('10/23/2010 8:21:46 AM' as datetime) as whenlogged, 10 as userid, 'mimmy (10) started session' as msg
-
union all
-
select '10/23/2010 8:07:21 AM', 12, 'mark (12) started session'
-
union all
-
select '10/22/2010 2:38:37 PM', 12, 'mark (12) started session'
-
union all
-
select '10/22/2010 2:27:21 PM', 32, 'grace (32) ended session'
-
union all
-
select '10/22/2010 2:11:01 PM', 32, 'grace (32) started session'
-
)
-
-
select
-
*
-
into session
-
from test_data
-
-
select * from session
-
-
Here's the code that will summarize your session table. You're going to have to convert the datetime column to varchar if you really want your query to return "?" for the missing end session. This query defaults it to NULL, instead. -
-
-
-
;with sorted_session
-
as
-
(
-
select
-
row_cnt = row_number() over(partition by userid order by whenlogged),
-
whenlogged, userid, msg
-
from session
-
)
-
select
-
logged_in.userid, logged_in.whenlogged as session_start,
-
session_id =
-
case
-
when logged_out.row_cnt is null then null
-
else logged_out.whenlogged
-
end
-
from sorted_session logged_in
-
left join sorted_session logged_out on logged_in.userid = logged_out.userid and logged_in.row_cnt + 1 = logged_out.row_cnt and logged_out.msg like '%ended session%'
-
where logged_in.msg like '%started%'
-
-
Just a suggestion, make the start/end session indicator as a code or a flag, not a string...
Happy Coding!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: StealthBananaT |
last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the
count of its reviews, MySQL locks and I have to restart the...
|
by: Jon Trelfa |
last post by:
I'm having difficulties getting all rows to return from the left table
on a left join. For some reason, i only get the rows where there is a
match between the right and the left table. Here's...
|
by: Quarco |
last post by:
Hi,
Suppose I have a query like:
SELECT
products.name AS product,
SUM(IF(stock.invoice=0,1,0)) AS in_stock,
SUM(IF(shopcart.status=1,1,0)) AS reserved
FROM products
LEFT JOIN stock ON...
|
by: rossz |
last post by:
I have this query to get the 10 most common products from in stock,
based upon which parent category they are in:
SELECT p.*,i.sql_inventory AS quantity
FROM products AS p
JOIN inv_cnt AS i ON...
|
by: deko |
last post by:
SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
FROM (tblTxAcct INNER JOIN tblTxType ON
tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt...
|
by: Ryan |
last post by:
I want to do the following SQL statement in Access. However, it won't
allow me to have the secondary part of my join statement and tells me
that this is not supported. OK, so Access doesn't support...
|
by: Darryl Kerkeslager |
last post by:
The following SQL does as I intend, but ...
SELECT offender_id, off_name, inv_ppo_id,
add_note, add_zip_id, it_name AS Purpose
FROM (offender INNER JOIN (investigation
INNER JOIN...
|
by: David F |
last post by:
Hello,
I have what seems like a simple left join query,
but Access returns results like it's an inner join. I've tried
numerous combinations without success.
I have a table (ProjectList)...
|
by: jim |
last post by:
Hi,
I've browsed several posts, but still haven't found the answer I'm
looking for. I have one table (A) that contains a list of values I
want to return. I have two other tables (B) and (C)...
|
by: polycom |
last post by:
Assistance needed to optimize this query
SELECT SD.content_id AS Id,
SD.title AS Title,
CT.name AS Contenttype,
PV.content_id AS SponsorId,
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |