Help me out in this query

Question

HI I have a table which contains 4 columns 1. ID 2. CreatedOn 3. UserId 4. Status I want calculate total Log in time and total offline time I will provide Date and User Id to my Stored Procedure . Here is the sample data of the table. Id CreatedOn UserId Status 70 2014-10-17 17:37:55.493 3 Log In 71 2014-10-17 17:40:27.960 3 Log Out 132 2014-10-20 11:35:18.080 3 Log In 133 2014-10-20 11:35:35.167 3 Log Out 134 2014-10-20 11:35:53.760 3 Log In 137 2014-10-20 11:37:11.397 3 Offline 140 2014-10-20 11:37:56.193 3 Online 141 2014-10-20 11:38:21.810 3 Log Out 473 2014-10-27 17:41:49.443 3 Log In 485 2014-10-27 17:49:51.930 3 Log Out 490 2014-10-28 11:01:23.073 3 Log In 492 2014-10-28 12:18:11.487 3 Log In 496 2014-10-28 13:26:21.097 3 Log In 498 2014-10-28 15:43:37.410 3 Log In 499 2014-10-28 16:04:16.243 3 Log In 502 2014-10-28 17:28:35.557 3 Log In 503 2014-10-28 18:23:22.953 3 Log In 504 2014-10-28 19:24:10.803 3 Log Out 508 2014-10-29 09:26:11.120 3 Log In 509 2015-01-12 19:59:03.537 3 Log In 510 2015-01-12 19:59:03.543 3 Offline

Vikram Agrawal · Answer

Well ID LoginDate LogoutDate UserId 1 2014-10-17 17:37 2014-10-17 17:50 u1 2 2014-10-17 18:20 2014-10-17 18:50 u1 3 2014-10-17 19:15 2014-10-17 19:40 u1 4 2014-10-17 20:00 2014-10-17 20:20 u1 5 2014-10-18 09:00 2014-10-18 11:00 u1 maintain your table like this and now you can get the duration with substracting loginDate with logOutDate i.e. @Seconds = DateDiff(SLoginDateLogoutDate) you will get number of seconds. These seconds you can convert it into hours and mins. Thanks.

Pradeep Shet · Answer

Hi find the below logic syntax can give error but try getting logic. declare @logouttime datetime @logintime datetime @offlinetime datetime @onlinetime datetime @tlogintime int = 0; declare @count int @i = 1; //create temp table for both login & logout seperately select * into #logintable where status = 'Log in'; select * into #logouttable where status = 'Log in'; //take total count of login as this will be the most. select @count=COUNT(1) from #logintable; while(@i!= @count) BEGIN select @logintime = createdon from #logintable where rowid = @i and status = 'Log in'; select @logouttime = createdon from #logouttable where rowid = @i and status = 'Log out'; //Find difference between the both login & logout. I considered in hours. select @tlogintime = @tlogintime + datediff(HH @logintime @logouttime); SET @i=@i +1; END //The above while loop will execute till all entries are done. This will give you total login time in hours. Note: You have handle for the last entry is login & no logout yet. the logout table may contain 1 entry less. SELECT @tlogintime AS 'Total Login Time'; Same case is for offline i.e- online-offline. Hope this answers your query. Plz mark it as answered if this code helped you .

Zeeshan Azim · Answer

Please send me your proposed schema with dummy data so that I may understand well. Write me the query as well.

Khargesh Rajput · Answer

you have to add columns for both online time and offline time

Vikram Agrawal · Answer

Hi Zeeshan For this requirement you need to maintain both Login Time as well as Logout Time too then you can calculate total login duration and offline duration.