Query on dates

[Mauricianstyle]

Hi,
I would like to make a query that count simultaneous connection.
Here is my tables
Users (ID_User, Name)
Date_logon (Id_on, Date_logon, time_logon, Num_user#)
Date_logoff (Id_logoff, Date_logoff, time_logoff, Num_user#)

Here is a part of a query that diplay users date and time of log on and log off.

Users Date_logon Time_logon Date_logoff Time_logoff
Utilisateur1 13/05/2015 17:38:42 13/05/2015 18:52:15
Utilisateur2 13/05/2015 17:12:21 13/05/2015 17:30:24
Utilisateur3 13/05/2015 16:59:08 13/05/2015 17:16:06
Utilisateur4 13/05/2015 16:54:14 13/05/2015 18:32:13

Then I put that result in a pivot table in Excel. In line we have time logon by date and in column we have time logoff by date.

Here is the issue :
In Excel for the 4 lines above, it counts 2 logon at 4PM and 2 logon at 5PM. We should have 4.

 

[vbaInet]

Not sure where you're going with this. Is this an Access query problem or an Excel pivot table problem?

 

[Mauricianstyle]

Hi,

I think it is an Access query problem but I am not sure.

Here is the same query as above but with more data.


Users Date_logon Time_logon Date_logoff Time_logoff
Users1 13/05/2015 17:38:42 13/05/2015 18:52:15
Users2 13/05/2015 17:12:21 13/05/2015 17:30:24
Users3 13/05/2015 16:59:08 13/05/2015 17:16:06
Users4 13/05/2015 16:54:14 13/05/2015 18:32:13
Users5 13/05/2015 10:54:14 13/05/2015 11:32:13
Users6 12/05/2015 16:00:00 12/05/2015 16:48:00
Users7 12/05/2015 15:30:15 12/05/2015 16:30:15



I would like that result in a pivot table or with a SQL query.

Simultaneous connections.
13/05/2015
4
1
12/05/2015
2

 

[vbaInet]

Why is "13/05/2015" 4, 1? Shouldn't it be 5 instead of 4, 1?
If you're looking at the time part of User5 then you could sort by Date_Logon > Time_Logon and it will be 5.

 

[spikepl]

Define "simultaneous " connection. Fx at 13/05/2015 18:32:14 until 18:52:15there is one, and 2 just prior to that earlier time.

So what are the precise criteria?

For snapshot in a continuous measurement, you could just sum all logins prior to a given time minus all logouts prior to that given time.

 

[Mauricianstyle]

Hi,

We have "4, 1" on "13/05/2015" because, user1, user2, user3 and user4 are connected at the same time.

 

[vbaInet]

Perhaps you could elaborate on how this is the case? From your data I can't see any indication that users 1,2,3 and 4 logged in at the same time.

Are there two shifts?

 

[Mauricianstyle]

Hi,

Here is the explanation of my chart.

User1 logged in the 13/05/2015 at 17:38:42 and logged out at 18:52:15
User2 logged in the 13/05/2015 at 17:12:21 and logged out at 17:30:24
User3 logged in the 13/05/2015 at 16:59:08 and logged out at 17:16:06
User4 logged in the 13/05/2015 at 16:54:14 and logged out at 18:32:13

So they are connected in the same time interval.

 

[vbaInet]

You're seeing this differently, but I'm still not getting the logic behind the 4 users being in the same "time interval".

User 1 between 17:00 and 19:00
User 2 between 17:00 and 18:00
User 3 between 16:00 and 18:00
User 4 between 16:00 and 19:00

So can you please explain how they fall in the same time interval? I think once you clarify this, we can work towards a solution.

 

[Mauricianstyle]

These data are event log from a software.
So user1, user2, user3 and user4 use the software at the same time.

 

[spikepl]

We cannot help you if you just repeat already given information and do not answer the questions.

How exactly do you define "at the same time" ? If you look at your log minute by minute, different number of users were on at different periods.

 

[Mauricianstyle]

User2 logged in the software from 17:12:21 to 17:30:24

AND

User3 logged in the software from 16:59:08 to 17:16:06

The time interval of user3 [16:59:08 to 17:16:06] is in the time interval of user2 [17:12:21 to 17:30:24].

So we have a maximum of 2 users who use the software.

Sorry I made a mistake, user1 is not in the same interval.

 

[vbaInet]

The time interval of user3 [16:59:08 to 17:16:06] is in the time interval of user2 [17:12:21 to 17:30:24].

So we have a maximum of 2 users who use the software.

Sorry I made a mistake, user1 is not in the same interval.

Now that's contradictory and I'm sure spikepl would agree. If you're talking about the number of concurrent users throughout the day, then all four users (1 to 4) were logged in concurrently.

Let's look at my analogy again:

User 1 between 17:00 and 19:00
User 2 between 17:00 and 18:00
User 3 between 16:00 and 18:00
User 4 between 16:00 and 19:00

You can see from above that all four of them overlap.

So if you're looking for overlapping start and end times then we can work towards that.

 

[Mauricianstyle]

Hi,

I am effectively looking for overlapping start and end times.

The aim of that query is to know how many licenses do we need for that software. It is a concurrent use license.

 

[vbaInet]

Ok, not tested but try something like this:

SELECT 	A.*,
	(
	 SELECT	Count(*)
	 FROM 	[COLOR="blue"]qryLogin [/COLOR]AS B
	 WHERE 	(
		 B.Time_logon > A.Time_logoff
	    OR	 B.Time_logoff < A.Time_logon
		) = FALSE
	   AND	B.Date_logon = A.Date_logon
	   AND	B.Date_logoff = A.Date_logoff
	)
FROM [COLOR="Blue"]qryLogin [/COLOR]AS A

Ensure that qryLogin is:
* GROUPed BY Date_Logon, Date_logoff
* ORDERed BY Time_logon, Time_logoff

 

[Mauricianstyle]

Hi,

Thank you very much for your answer.
I will try this week end.

 

[Mauricianstyle]

Hi,

Please find attached the result of the query.

I have a result on each line.

Thank you

 

[vbaInet]

Let me see the SQL of both queries.

 

[Mauricianstyle]

To test that query I have just 1 table in which I import few records.

LOG (ID, User, Date_logon, Time_logon, Date_logoff, Time_logoff)

SELECT A.*, (SELECT     Count(*)
     FROM     LOG AS B
     WHERE     (
         B.Heure_connexion > A.Heure_deconnexion
        OR     B.Heure_deconnexion < A.Heure_connexion
        ) = FALSE
           AND     B.Date_connexion = A.Date_connexion
       AND     B.Date_deconnexion = A.Date_deconnexion
    )
FROM LOG AS A;

Here is a file in which we have the table and the query.

 

[vbaInet]

The query was ideal only when the date is the same as per your original examples, but since in the real world you're going to have other dates, it needed to be tweaked.

See attached.