Help me out in this query

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.

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

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.