SQL Server Query to find the duration in given time period

tpxzln5u  于 2023-05-05  发布在  其他
关注(0)|答案(2)|浏览(126)

I have a requirement to find the logged in time of a user on the given time range in SQL server. The table has login_time and logout_time per user.

Sample data.

id   user_id    login_time            logout_time
1     101       2023-05-02 14:10:00   2023-05-04 09:30:00

So If I search the report for duration between 2023-05-01 and 2023-05-05, the desired output is

date           user_id  login_duration_seconds
2023-05-01      101      0
2023-05-02      101      35400
2023-05-03      101      86400
2023-05-04      101      34200
2023-05-05      101      0

Can someone please help me build a query for the same.

2izufjch

2izufjch1#

Try the following:

DECLARE @start_date DATE = '2023-05-01';
DECLARE @end_date DATE = '2023-05-05';

WITH tbl AS
(
  SELECT 1 id, 101 user_id, 
    CAST('2023-05-02 14:10' AS DATETIME) login_time,
    CAST('2023-05-04 9:30' AS DATETIME) logout_time 
)
,all_dates AS -- using row_number from the [master]..spt_values table to generate all possible times.
(
  SELECT tb.user_id, T.dt
  FROM (
    SELECT TOP (DATEDIFF(DAY, @start_date, @end_date)+1)  
     DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1,  @start_date) AS dt
    FROM [master]..spt_values
    ) T
  CROSS JOIN (SELECT DISTINCT user_id FROM tbl) tb
)

SELECT ad.dt [Date], ad.user_id, 
  CASE 
   WHEN tb.login_time IS NULL THEN 0
   WHEN CAST(tb.login_time AS DATE) = CAST(tb.logout_time AS DATE) THEN DATEDIFF(SECOND,tb.logout_time, tb.login_time)
   WHEN ad.dt = CAST(tb.login_time AS DATE) THEN 24*60*60 - DATEDIFF(SECOND,ad.dt, tb.login_time)
   WHEN ad.dt = CAST(tb.logout_time AS DATE) THEN DATEDIFF(SECOND,ad.dt, tb.logout_time)
   ELSE 24*60*60 -- Full day
  END login_duration_seconds
FROM all_dates ad LEFT JOIN tbl tb
ON ad.dt BETWEEN CAST(tb.login_time AS DATE) AND CAST(tb.logout_time AS DATE)
 AND tb.user_id = ad.user_id
WHERE ad.user_id = 101
ORDER BY user_id, [Date]

Output:

Date        user_id  login_duration_seconds
2023-05-01  101      0
2023-05-02  101      35400
2023-05-03  101      86400
2023-05-04  101      34200
2023-05-05  101      0
hsvhsicv

hsvhsicv2#

Assume that you have login_history table.

Here is a query

SELECT DATE(login_time) AS date, user_id, SUM(TIME_TO_SEC(TIMEDIFF(logout_time, login_time))) AS login_duration_seconds
FROM login_history
WHERE login_time BETWEEN '2023-05-01 00:00:00' AND '2023-05-05 23:59:59'
GROUP BY DATE(login_time), user_id

Here is a updated query

SELECT 
    date_range.date,
    login_history.user_id,
    COALESCE(login_history.login_duration_seconds, 0) AS login_duration_seconds
FROM 
    (SELECT DATE('2023-05-01') AS date
     UNION ALL SELECT DATE('2023-05-02') AS date
     UNION ALL SELECT DATE('2023-05-03') AS date
     UNION ALL SELECT DATE('2023-05-04') AS date
     UNION ALL SELECT DATE('2023-05-05') AS date) AS date_range
LEFT JOIN 
    login_history 
ON 
    date_range.date = login_history.date AND 
    date_range.user_id = login_history.user_id
ORDER BY 
    date_range.date ASC, 
    login_history.user_id ASC;

相关问题