Hello all,

I have a SQL database and there is a table with all login sessions (with beginn and end in UNIX timestamp).

I now try to find a select query or something to count the logins in a period of time (week, month, year).

But if a person logs in, from that beginn all of his logins and outs have to be not count from the begin for 24h.

Example:

Mike Start 01.01.2013 - 12:00 End 01.01.2013 - 12:33

Mike Start 01.01.2013 - 15:22 End 01.01.2013 - 16:53

Mike Start 01.01.2013 - 20:01 End 01.01.2013 - 21:49

Mike Start 02.01.2013 - 15:00 End 02.01.2013 - 17:33

Mike Start 02.01.2013 - 18:21 End 02.01.2013 - 20:11

....

Has to be count as 2, because Mike start on 01.01.2013 - 12:00 so from there for 24h (mean until 02.01.2013) is only one. Second and third login are in this period and so not counted. Then the 24h from first login are over. He logs in again, gets count, but next session is in this period of 24h and not count, again.

I know that seems to bee not very easy. I think for this query are only Loginname, Starttime and Endtime necessary.

I tryed with something like that:

SELECTCASE WHEN Loginname = Loginname and StartTime between StartTime and StartTime + 86400 and Endtime between StartTime and StartTime + 86400

            THEN ....

       END

FROM Table

You can also use an Update funktion or what ever you think is necessary.

I hope you can help me with that.

kind regards

Mario

do you mean something like this?

declare @t table
(
Name varchar(100),
StartTime datetime,
ENdTime datetime
)
insert @t
 values ('Mike','01.01.2013 12:00','01.01.2013 12:33'),

('Mike','01.01.2013 15:22','01.01.2013 16:53'),

('Mike','01.01.2013 20:01','01.01.2013 21:49'),

('Mike','02.01.2013 15:00','02.01.2013 17:33'),

('Mike','02.01.2013 18:21','02.01.2013 20:11'),

('Mike','02.01.2013 20:21','02.01.2013 22:11'),

('Mike','03.01.2013 02:21','03.01.2013 04:11')

SELECT Name,
COUNT(DISTINCT DATEADD(dd,DATEDIFF(dd,0,Start),0))
FROM
(
SELECT Name,DATEADD(ss,-DATEDIFF(ss,0,MinStart),StartTime) AS Start,DATEADD(ss,-DATEDIFF(ss,0,MinStart),EndTime) AS [ENd]
FROM @t t
CROSS APPLY (SELECT DATEADD(dd,-DATEDIFF(dd,0,MIN(StartTime)),MIN(StartTime)) AS MinSTart
             FROM @t       
			 WHERE Name = t.Name)t1
			 )t
			 GROUP BY Name

Hello Visakh,

your solution seems to be working for that 5 example values.

But my database has thousands of sessions and I can not put them all in there manually.

I need to take the values/data sets from the table.

Can I do that with something like:

insert into @t (Name, StartTime, EndTime)

from Logintable

instead of

insert @t

values...

?

Kind regards

Mario

You can

I gave those 5 rows just for illustrating your scenario :)

you can replace @t with your actual table containing the full data.

Hello Visakh,

I have tested your solution with some cases and found two problems.

I made a picture and hope that can help with the understanding.

The problems are also shown there.

I hope you can help me fixing this issues.

Oh I can not add pictures or links here. :(

---http://s14.directupload.net/images/131203/afnys7ut.jpg

If there are some questions left, please ask. :)

kind regards

Mario

• Edited by Mario Clarson Wednesday, December 04, 2013 6:35 AM

Hello Visakh,

I have tested your solution with some cases and found two problems.

I made a picture and hope that can help with the understanding.

The problems are also shown there.

I hope you can help me fixing this issues.

Oh I can not add pictures or links here. :(

---http://s14.directupload.net/images/131203/afnys7ut.jpg

If there are some questions left, please ask. :)

kind regards

Mario

• Edited by Mario Clarson Wednesday, December 04, 2013 6:35 AM

Try this,

declare @t table(Name varchar(100),StartTime datetime,ENdTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33')
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53')
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49')
insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33')
insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11')
insert into @t  values ('Mike','02.01.2013 20:21','02.01.2013 22:11')
insert into @t  values ('Mike','03.01.2013 02:21','03.01.2013 04:11')
SELECT [Name], Count(StartTime)  SessionCount FROM (
	SELECT [Name], convert(char(10),StartTime,101) StartTime    
	FROM @t
	GROUP BY [Name], convert(char(10),StartTime,101)
) X
GROUP BY [Name]

Hello RSingh,

thanks for you help.

I think there are the same problems as on Visakhs solution.

Your solution only mentions the day of the Starttime.

If the session goes from one day to an other, the other day is not counted.

I tried to draw a picture, to make it easyer to understand:

---http://s14.directupload.net/images/131203/afnys7ut.jpg

What about this one,

SELECT [Name], Count(StartTime)  SessionCount FROM (
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) >= 1
 GROUP BY [Name], convert(char(10),StartTime,101)
 UNION ALL
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) = 0
 GROUP BY [Name], convert(char(10),StartTime,101)
) X
GROUP BY [Name]

Hello RSingh,

thanks for your help, but it seems not to be the right solution. If I try this:

declare @t table(Name varchar(100),StartTime datetime,ENdTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33') --1
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53') --1
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49') --1
insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33') --2
insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11') --2
insert into @t  values ('Mike','03.01.2013 14:21','03.01.2013 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t  values ('Mike','04.01.2013 02:21','04.01.2013 04:13') --3
insert into @t  values ('Moes','02.01.2013 18:21','02.01.2013 20:11') --4
insert into @t  values ('Moes','02.01.2013 20:21','02.01.2013 22:11') --4
insert into @t  values ('Moes','03.01.2013 02:21','03.01.2013 04:11') --4
-- 1 : Mike 01.01.2013 12:00 - 02.01.2013 11:59(:59)
-- 2 : Mike 02.01.2013 15:00 - 03.01.2013 14:59(:59)
-- 3 : Mike 03.01.2013 15:00 - 04.01.2013 14:59(:59)
-- 4 : Moes 02.01.2013 18:21 - 03.01.2013 18:20(:59)
SELECT [Name], Count(StartTime)  SessionCount FROM (
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) >= 1
 GROUP BY [Name], convert(char(10),StartTime,101)
 UNION ALL
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) = 0
 GROUP BY [Name], convert(char(10),StartTime,101)
) X
GROUP BY [Name]

Your query gives as result:

Mike: 4

Moes:2

But it should be:

Mike: 3

Moes:1

as you can see in the comments above.

kind regards

Mario

• Edited by Mario Clarson 4 hours 11 minutes ago

Hello RSingh,

thanks for your help, but it seems not to be the right solution. If I try this:

declare @t table(Name varchar(100),StartTime datetime,ENdTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33') --1
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53') --1
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49') --1
insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33') --2
insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11') --2
insert into @t  values ('Mike','03.01.2013 14:21','03.01.2013 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t  values ('Mike','04.01.2013 02:21','04.01.2013 04:13') --3
insert into @t  values ('Moes','02.01.2013 18:21','02.01.2013 20:11') --4
insert into @t  values ('Moes','02.01.2013 20:21','02.01.2013 22:11') --4
insert into @t  values ('Moes','03.01.2013 02:21','03.01.2013 04:11') --4
-- 1 : Mike 01.01.2013 12:00 - 02.01.2013 11:59(:59)
-- 2 : Mike 02.01.2013 15:00 - 03.01.2013 14:59(:59)
-- 3 : Mike 03.01.2013 15:00 - 04.01.2013 14:59(:59)
-- 4 : Moes 02.01.2013 18:21 - 03.01.2013 18:20(:59)
SELECT [Name], Count(StartTime)  SessionCount FROM (
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) >= 1
 GROUP BY [Name], convert(char(10),StartTime,101)
 UNION ALL
 SELECT [Name], convert(char(10),StartTime,101) StartTime   
 FROM @t WHERE datediff(d, StartTime, EndTime) = 0
 GROUP BY [Name], convert(char(10),StartTime,101)
) X
GROUP BY [Name]

Your query gives as result:

Mike: 4

Moes:2

But it should be:

Mike: 3

Moes:1

as you can see in the comments above.

kind regards

Mario

• Edited by Mario Clarson Monday, December 09, 2013 7:22 AM

Hi Mario,

You may try this one,

declare @t table(Name varchar(100),StartTime datetime,ENdTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33') --1
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53') --1
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49') --1
insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33') --2
insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11') --2
insert into @t  values ('Mike','03.01.2013 14:21','03.01.2013 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t  values ('Mike','04.01.2013 02:21','04.01.2013 04:13') --3
insert into @t  values ('Moes','02.01.2013 18:21','02.01.2013 20:11') --4
insert into @t  values ('Moes','02.01.2013 20:21','02.01.2013 22:11') --4
insert into @t  values ('Moes','03.01.2013 02:21','03.01.2013 04:11') --4
-- 1 : Mike 01.01.2013 12:00 - 02.01.2013 11:59(:59)
-- 2 : Mike 02.01.2013 15:00 - 03.01.2013 14:59(:59)
-- 3 : Mike 03.01.2013 15:00 - 04.01.2013 14:59(:59)
-- 4 : Moes 02.01.2013 18:21 - 03.01.2013 18:20(:59)
;WITH CTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY NAME,STARTTIME) ID,*, 
	DATEADD(HH,24, STARTTIME) [STARTTIME+24], 
	DATEDIFF(D,STARTTIME,ENDTIME) DAYDIFF 
	FROM (
	SELECT NAME,MIN(STARTTIME) STARTTIME ,MAX(ENDTIME) ENDTIME
	FROM @t
	GROUP BY NAME,CONVERT(CHAR(10),STARTTIME,101)
) X
), CTE_FINAL AS (
	SELECT *,CASE
	WHEN [STARTTIME+24] < (SELECT STARTTIME FROM CTE B WHERE B.ID = A.ID+1) THEN 1 + DAYDIFF
	ELSE 0 + DAYDIFF END COUNTING
	FROM CTE A
) 
SELECT NAME,SUM(COUNTING) SESSIONCOUNT FROM CTE_FINAL
GROUP BY NAME

Hello RSingh,

first let me thank you for trying to solve my problem. I am very impressed about your efforts.

But if I try your new solution, I now get as result:

Mike: 1

Moes: 0

But it should be:

Mike: 3

Moes: 1

I tried to follow your query to find out why it is not working as I would like to have it, but I do not understand some of the things you did.

For example you made the tables @t, CTE and CTE_FINAL, but in the CASE you take values from table CTE B and CTE A. I don't now how that works because for my understanding there does not exist neither a table CTE B nor CTE A.

You also use B.ID and A.ID, but I think you only have ID, so where are B.ID and A.ID coming from?

And why ever they exist, why you have both values B.ID and A.ID in table CTE B?

Yours respectfully

Mario

I am getting the below count when i try my previous post. My system date format is MM/DD/YYYY.

Mike: 3

Moes: 1

Regarding your queries, let me try to explain one by one below,

;WITH CTE AS ( -- step 2: I add a row number to identify each row return in step 1, also add 2 computed items. 
				-- First computed item is to add 24 hour to StartTime, just to check whether the Enddate or the next row StartTime fall 
				-- within 24 hour from Start date or not.
				-- Second item is calculating day difference between StartTime and EndTime. If there is a day difference then 
				-- i count it as a 1 day by default else 0 day. This CTE represents the result set return by the below
				-- SELECT statement. A CTE can be call from another CTE i.e CTE_FINAL. Read Common Table Expression.
	SELECT ROW_NUMBER() OVER (ORDER BY NAME,STARTTIME) ID,*, 
	DATEADD(HH,24, STARTTIME) [STARTTIME+24], 
	DATEDIFF(D,STARTTIME,ENDTIME) DAYDIFF 
	FROM ( --step 1: below SELECT will group all names base on StartDate (excluding timestamp) and get the minimum start time 
			-- and maximum end time. For 'Mike' it will 
			-- group into 4 rows becouse we have 4 different StartDate. 
			-- You can try running the below select query outside CTE(just after insert statements)
		SELECT NAME,MIN(STARTTIME) STARTTIME ,MAX(ENDTIME) ENDTIME
		FROM @t
		GROUP BY NAME,CONVERT(CHAR(10),STARTTIME,101
		)
) X
), CTE_FINAL AS ( -- step 3: Here i am calling the above CTE and given an alias name 'A'. 
					-- I add a CASE expression to check whether the next row StartTime is withing the 24 Hour from Previous row StartTime
					-- I am comparing consecutive rows. 
					-- The ID which i have added in the CTE plays an important role to identify current and previous row in CTE.
					-- This can be accomplish by using CTE twice with different alias name as 'CTE A' and 'CTE B'.
					-- i.e B.ID = A.ID+1. 'B' is another alias name of CTE.
	SELECT *,CASE
	WHEN [STARTTIME+24] < (SELECT STARTTIME FROM CTE B WHERE B.ID = A.ID+1) THEN 1 + DAYDIFF
	ELSE 0 + DAYDIFF END COUNTING
	FROM CTE A
) 
-- finally call CTE_FINAL AND SUM THE COUNTING FIELD.
SELECT NAME,SUM(COUNTING) SESSIONCOUNT FROM CTE_FINAL
GROUP BY NAME

Regards, RSingh

• Edited by RSingh()Editor Friday, December 13, 2013 5:30 AM

Hello RSingh,

many thanks for your good explanation. That was very helpfull.

So it seems that my system date format is different from yours.

For my understanding I have to convert it to get it running properly.

Maybe with:

CONVERT (datetime, STARTTIME, 101)

CONVERT (datetime, ENDTIME, 101)

Then I should have the same date format than you and get the same output.

But where to put it in your complex query? Because you are already converting STARTTIME in step 1.

best regards

Mario

Try replacing "CONVERT(CHAR(10),STARTTIME,101)" with "CONVERT(CHAR(10),STARTTIME,103)"

Can you post the result of CTE ? That will give a picture what is going on there.

You need to write as,

;WITH CTE as (

----step 1 and step 2

)

SELECT * FR

Hi RSingh

I replaced "CONVERT(CHAR(10),STARTTIME,101)" with "CONVERT(CHAR(10),STARTTIME,103)"

But I get the same result:

Mike: 1

Moe: 0

So I did the query as you described only with step 1 and step 2 and the result is:

ID      NAME    STARTTIME               ENDTIME                 STARTTIME+24            DAYDIFF
1	Mike	2013-01-01 12:00:00.000	2013-01-01 21:49:00.000	2013-01-02 12:00:00.000	0
2	Mike	2013-01-02 15:00:00.000	2013-01-02 20:11:00.000	2013-01-03 15:00:00.000	0
3	Mike	2013-01-03 14:21:00.000	2013-01-03 22:11:00.000	2013-01-04 14:21:00.000	0
4	Mike	2013-01-04 02:21:00.000	2013-01-04 04:13:00.000	2013-01-05 02:21:00.000	0
5	Moes	2013-01-02 18:21:00.000	2013-01-02 22:11:00.000	2013-01-03 18:21:00.000	0
6	Moes	2013-01-03 02:21:00.000	2013-01-03 04:11:00.000	2013-01-04 02:21:00.000	0

I did that with "CONVERT(CHAR(10),STARTTIME,101)" as well as "CONVERT(CHAR(10),STARTTIME,103)" but the output is 100% the same.

So I have a problem with the conversion of date and time, haven't I?

Not sure what is hapenning. CTE results looks ok and almost similar to my result though there is differenct in date format.

What about CTE_FINAL ? Can you post the result.  Lets us see the counting column. Write "SELECT * FROM CTE_FINAL" just after the ")" of CTE_FINAL.  May be the CASE expression is not working properly.

Hello RSingh

I did what you mentioned and get the following result:

ID      NAME    STARTTIME               ENDTIME                 STARTTIME+24            DAYDIFF COUNTING
1	Mike	2013-01-01 12:00:00.000	2013-01-01 21:49:00.000	2013-01-02 12:00:00.000	0	1
2	Mike	2013-01-02 15:00:00.000	2013-01-02 20:11:00.000	2013-01-03 15:00:00.000	0	0
3	Mike	2013-01-03 14:21:00.000	2013-01-03 22:11:00.000	2013-01-04 14:21:00.000	0	0
4	Mike	2013-01-04 02:21:00.000	2013-01-04 04:13:00.000	2013-01-05 02:21:00.000	0	0
5	Moes	2013-01-02 18:21:00.000	2013-01-02 22:11:00.000	2013-01-03 18:21:00.000	0	0
6	Moes	2013-01-03 02:21:00.000	2013-01-03 04:11:00.000	2013-01-04 02:21:00.000	0	0

I did that with "CONVERT(CHAR(10),STARTTIME,101)" as well as "CONVERT(CHAR(10),STARTTIME,103)" but the output is 100% the same.

Regards,

Mario

In the CASE expression of CTE_Final change the "<" operator to ">" and try once.

Hi RSingh,

now I get the results back, which I have expected:

Mike: 3

Moe: 1

I will now test the query with different data sets in different scenarios and then give you a feedback again.

But right now I can say many thanks for your great help.

kind regards,

Mario

Hello RSingh,

I now did some tests and found a problem. The problem seems to be that if the is the STARTTIME+24 between an other session (between STARTTIME and ENDTIME) there has to be set a new sassion start, but it is not. Here two examples, where you can see the problem (I just took some datasets out with comment):

declare @t table(Name varchar(100),StartTime datetime,EndTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33') --1
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53') --1
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49') --1
--insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33') 
--insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11') 
--insert into @t  values ('Mike','03.01.2013 14:21','03.01.2013 22:11') 
insert into @t  values ('Mike','04.01.2013 02:21','04.01.2013 04:13') --2
insert into @t  values ('Moes','02.01.2013 18:21','02.01.2013 20:11') --3
insert into @t  values ('Moes','02.01.2013 20:21','02.01.2013 22:11') --3
insert into @t  values ('Moes','03.01.2013 02:21','03.01.2013 04:11') --4
-- Sessions should be:
-- 1 : Mike 01.01.2013 12:00 - 02.01.2013 12:00
-- 2 : Mike 04.01.2013 02:21 - 05.01.2013 02:21
-- 3 : Moes 02.01.2013 18:21 - 03.01.2013 18:21
;WITH CTE AS (
...

So the output should be:

Mike: 2

Moes: 1

But I get:

Mike: 1

Moes: 1

And if you try the following:

declare @t table(Name varchar(100),StartTime datetime,EndTime datetime)
insert into @t  values ('Mike','01.01.2013 12:00','01.01.2013 12:33') --1
insert into @t  values ('Mike','01.01.2013 15:22','01.01.2013 16:53') --1
insert into @t  values ('Mike','01.01.2013 20:01','01.01.2013 21:49') --1
insert into @t  values ('Mike','02.01.2013 15:00','02.01.2013 17:33') --2
insert into @t  values ('Mike','02.01.2013 18:21','02.01.2013 20:11') --2
--insert into @t  values ('Mike','03.01.2013 14:21','03.01.2013 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t  values ('Mike','04.01.2013 02:21','04.01.2013 04:13') --3
insert into @t  values ('Moes','02.01.2013 18:21','02.01.2013 20:11') --4
insert into @t  values ('Moes','02.01.2013 20:21','02.01.2013 22:11') --4
insert into @t  values ('Moes','03.01.2013 02:21','03.01.2013 04:11') --4
-- Sessions shoud be:
-- 1 : Mike 01.01.2013 12:00 - 02.01.2013 12:00
-- 2 : Mike 02.01.2013 15:00 - 03.01.2013 15:00
-- 3 : Mike 04.01.2013 02:21 - 05.01.2013 02:21
-- 4 : Moes 02.01.2013 18:21 - 03.01.2013 18:21
;WITH CTE AS (
...

I would expect:

Mike: 3

Moes: 1

But I get:

Mike: 1

Moes: 1

So I guess there will be something needed that defines the STARTTIME+24 as a new STARTTIME for the session if the STARTTIME+24 is between a STARTTIME and ENDTIME of that session.

What do you think?

kind regards

Mario

So I guess there will be something needed that defines the STARTTIME+24 as a new STARTTIME for the session if the STARTTIME+24 is between a STARTTIME and ENDTIME of that session.

Yes you are right. Capturing the new STARTTIME is a challenge. The new STARTTIME will impact the subsequent login sessions range for the user.

I think we can try once using the CURSOR.

May be you can wait for other EXPERTS to comment on this one.