Select as Login counter for sessions in a period of time

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

  • Moved by RSingh() Saturday, December 21, 2013 12:30 AM Seeking help from T-SQL experts. A partial solution has been provided however the OP need a specific functionality to implement in T-SQL. Please go through the reply chain.
December 3rd, 2013 1:32pm

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


Free Windows Admin Tool Kit Click here and download it now
December 3rd, 2013 3:44pm

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

December 9th, 2013 10:20am

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() Friday, December 13, 2013 5:30 AM
Free Windows Admin Tool Kit Click here and download it now
December 11th, 2013 8:25pm

Indeed an iterative solution is the way to go here, but before I go there, permit to discuss date formats, since this caused some confusion in this thread.

In most countries, people write dates as day-month-year with some delimiter. In a few, but whereof some are very popolous, people use year-month-day. And then there is a single country (but which is the third largest by population) where they use month-day-year. In international contexts, like these forums, it is recommendable to use year-month-day with four digit years, as that format can not be misunderstood by other forum participants.

In SQL Server, the best format is YYYYMMDD, because that format is always interpreted the same. SQL Server is able to misinterpret the format YYYY-MM-DD in some settings, so be careful.

Anyway, over to the acutal problem, which certainly goes beyond what you could expect in a Getting Started forum. It's a non-trivial problem, particularly, if the requirement is to achieve a single-statement solution. I have not tried to do so, because I quickly realised that this problem belongs to the class of problems which can be solved in a single pass over the data, but where there is no syntax in T-SQL to express this. For such problems, some sort of iteration is usually more efficient, and my assumption is that efficiency is more important than beauty.

I have however chosen to use so-called set-based iteration. That is, I number the rows in batches, and then I iterate over the batches, handling all rows in a batch in one go. This is usually a performance winner.

Before I give the solution, I should also try to explain how I have interpreted the problem: count slots of 24 hours, where an account has been active. A slot starts when the account logs in. If the account is logged in at this point 24h later, a new slot starts at that point. Else the next slot starts when the next login starts.

Here is my solution:

declare @t table(Name varchar(100),StartTime datetime,EndTime datetime)
insert into @t  values ('Mike','20130101 12:00','20130101 12:33') --1
insert into @t  values ('Mike','20130101 15:22','20130101 16:53') --1
insert into @t  values ('Mike','20130101 20:01','20130101 21:49') --1
insert into @t  values ('Mike','20130102 15:00','20130102 17:33') --2
insert into @t  values ('Mike','20130102 18:21','20130102 20:11') --2
insert into @t  values ('Mike','20130103 14:21','20130103 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t  values ('Mike','20130104 02:21','20130104 04:13') --3
insert into @t  values ('Moes','20130102 18:21','20130102 20:11') --1
insert into @t  values ('Moes','20130102 20:21','20130102 22:11') --2
insert into @t  values ('Moes','20130103 02:21','20130103 04:11') --2
insert into @t  values ('Moes','20130103 17:21','20130106 20:10') --2, 3, 4, 5

DECLARE @temp TABLE (batchno      int          NOT NULL,
                     Name         varchar(100) NOT NULL,
                     StartTime    int          NOT NULL,
                     EndTime      int          NOT NULL,
                     PRIMARY KEY (batchno, Name)
)

DECLARE @names TABLE (Name      varchar(100) NOT NULL PRIMARY KEY,
                      Start24h  int          NOT NULL,
                      NoOf24h   int          NOT NULL
)

INSERT @temp(batchno, Name, StartTime, EndTime)
   SELECT row_number() OVER(PARTITION BY Name ORDER BY StartTime),
          Name, datediff(ss, '19700101', StartTime),
                datediff(ss, '19700101', EndTime)
   FROM   @t

INSERT @names (Name, Start24h, NoOf24h)
   SELECT Name, StartTime, 1
   FROM   @temp
   WHERE  batchno = 1

DECLARE @batchno int = 2

WHILE EXISTS (SELECT * FROM @temp WHERE batchno = @batchno)
BEGIN
   UPDATE @names
   SET    Start24h =  CASE WHEN t.EndTime - n.Start24h < 86400
                           THEN n.Start24h
                           WHEN t.StartTime - n.Start24h > 86400
                           THEN t.StartTime
                           ELSE t.StartTime + 86400 * ((t.EndTime - t.StartTime) / 86400 + 1)
                      END,
          NoOf24h  += CASE WHEN t.EndTime - n.Start24h < 86400
                           THEN 0
                           ELSE (t.EndTime -
                                   CASE WHEN t.StartTime > n.Start24h + 86400
                                        THEN t.StartTime
                                        ELSE n.Start24h + 86400
                                   END) / 86400 + 1
                      END
   FROM   @names n
   JOIN   @temp  t ON t.Name = n.Name
   WHERE  t.batchno = @batchno

   SELECT @batchno += 1
END

SELECT Name, NoOf24h
FROM   @names
ORDER  BY Name

Free Windows Admin Tool Kit Click here and download it now
December 21st, 2013 4:40pm

At first let me say I am very sorry for my late reply (I was on holiday).

Thank you very much RSingh for all your help and moving the thread in the right section.

Hello Erland Sommarskog,

as far as I see, you interpreted my problem in the right way. Very many thanks for your help.

I will try and test your solution with many cases, but what I have seen until now looks great.

I will come back to you, to let you now if it works 100%.

kind regards

Mario

January 3rd, 2014 11:09am

Seems to work fine. Can not find any problems so far.

Many thanks.

Mario

I now tried to use the data of my database/table for/in that query, but I don't get it working.

I think I only need to replace some values in this part here (marked big and underlined):

.....

INSERT @temp(batchno, Name, StartTime, EndTime)
   SELECT row_number() OVER(PARTITION BY Name ORDER BY StartTime),
          Name, datediff(ss, '19700101', StartTime),
                datediff(ss, '19700101', EndTime)
   FROM   @t

......

Because all others are just temporary declared or am I wrong?

And my second problem is that the StartTime and EndTime of my table are in unix time. So I have to convert them somehow, haven't I?

I receive the following error message:

The datediff function resulted in an overflow. The number of 
dateparts separating two date/time instances is too large. Try to use datediff with a 
less precise datepart.

I hope that is no big problem.

Free Windows Admin Tool Kit Click here and download it now
January 6th, 2014 1:29pm

Hmm ok thread is marked as answered.

I think nobody will look into here again and help with the problem of the implementation. :-S

January 8th, 2014 10:06am

It was a pleasure to help!

I think nobody will look into here again and help with the problem of the implementation. :-S

Don't say that. One point with these forums is that people can find solutionss that already have been posted. I would not say that your problem was particularly odd, but it seems likely that someone else could run into the same. Now, the big challenge may be find the right magic words on Google!

Free Windows Admin Tool Kit Click here and download it now
January 9th, 2014 1:26am

Hello Mr Sommarskog,

I am happy to hear that.

I took the challenge and used Google to get the query suiting my database. It took some time, but now it is working. Then I compared some random counting values from the query with manually self counted values and I found some that were not correct.

So I started to find out where the problem was. I first thought it comes from the conversion of your query to my database, but I found nothing. Then I checked some further cases with the declared test values and found 2 things which are not respected in the query.

I tried to get that cases fixed by my self but I didn't get it until now. Maybe you can have a look at it. I will explain it with some declared test values:

declare @t table(Name varchar(100),StartTime datetime,EndTime datetime)
insert into @t  values ('Mike','20130102 15:00','20130102 18:00') --1
insert into @t  values ('Mike','20130103 14:00','20130103 22:00') --1 and 2
insert into @t  values ('Mike','20130104 14:00','20130104 22:00') --2 and 3
insert into @t  values ('Mike','20130105 14:00','20130105 22:00') --3 and 4
insert into @t  values ('Mike','20130106 14:00','20130106 22:00') --4 and 5
--insert into @t  values ('Moes','20130102 21:00','20130102 23:00') --1
insert into @t  values ('Moes','20130103 17:00','20130106 20:00') --1, 2, 3, 4

I expect the following for Mike:

Mike Session count as 1:  Start 02.01.2013 15:00 - End 03.01.2013 15:00 (End is in the next login window:)

Mike Session count as 2:  Start 03.01.2013 15:00 - End 04.01.2013 15:00 (End is in the next login window again:)

Mike Session count as 3:  Start 04.01.2013 15:00 - End 05.01.2013 15:00 (End is in the next login window again:)

Mike Session count as 4:  Start 05.01.2013 15:00 - End 06.01.2013 15:00 (End is in the next login window again:)

Mike Session count as 5:  Start 06.01.2013 15:00 - End 05.01.2013 15:00

I expect the following for Moes:

Moes Session count as 1:  Start 03.01.2013 17:00 - End 04.01.2013 17:00 (End is in the same login window, but 24h over so make a new one:)

Moes Session count as 2:  Start 04.01.2013 17:00 - End 05.01.2013 17:00 (End is still in the same login window, but 24h over again so make a new one:)

Moes Session count as 3:  Start 05.01.2013 17:00 - End 06.01.2013 17:00 (End is still in the same login window, but 24h over again so make a new one:)

Moes Session count as 4:  Start 06.01.2013 17:00 - End 07.01.2013 17:00

So I would expect from the query:

Mike: 5

Moes: 4

But I get:

Mike: 3

Moes: 1

So in my opinion the query has two problems:

1. If you have 1. session and the +24h end is in between the next login window it makes it right and sets a new start(+24h). But if from that new start(+24h) +24h is again in between an other login window it does not detect that and does not set a new start. So I think that procedure has to be looped somehow for such a case.

2. There is a problem if the login window is longer than 24h. If you take the one session from Moes which is in comment to the query, you get as result Moes: 4 which is correct. But if you don't have that session whoes +24h end is in this the long session (longer than 24h) it will only be count as 1. I thing there are no +24starts set in itself. So that case should also be included somehow.

I hope you understand what I mean and can see where the problems are. Maybe you can help me a little bit again. I am trying on my own, but until now without any sucess.

kind regards

Mario

January 17th, 2014 4:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics