CODEWITHSUNDEEP
sqlsql-serverssms
dragos_kai
dragos_kai

Reputation: 110

SQL: Date differences for dates in the same column

I'm trying to get hours logged in for each of our CSRs.

The query I am trying to run to get data is:

SELECT  T1.user_log_id,
        T1.[user], 
        min(T1.event_date) AS Date1, 
        max(T2.event_date) AS Date2, 
       DATEDIFF(hour, T1.event_date, T2.event_date) AS DaysDiff
FROM    leads.dbo.lds_User_log T1
        JOIN leads.dbo.lds_User_log  T2
            ON T1.user_log_id = T2.user_log_id
            --AND T2.event_date >= T1.event_date
WHERE T1.event_date between '2018-05-18 00:00:01' AND '2018-05-18 23:00:01' 
GROUP BY T1.user_log_id, T1.event_Date, T2.event_date, T1.[user];

and produces: enter image description here

Inside of the table we have:

enter image description here

honestly I have no idea how to feed the data into itself to pull in a LOGIN and a LOGOUT event from the DB to properly run a DATEDIFF, and am legit lost.

Upvotes: 1

Views: 49

Answers (1)

Gonzalo Garcia Cordoba
Gonzalo Garcia Cordoba

Reputation: 61

The problem you are having is that you are calculating a max or min from an element used in the group by. You should remove them from the group by. And if you can't use the Date1 & Date2 variables in the diff, this depends on the DB you are executing this query, you can't try it by using pre-calculated tables in the FROM and JOIN.

You can try the following solutions:

SELECT  T1.user_log_id,
        T1.[user], 
        min(T1.event_date) AS Date1, 
        max(T2.event_date) AS Date2, 
       DATEDIFF(hour, Date1, Date2) AS DaysDiff
FROM    leads.dbo.lds_User_log T1
        JOIN leads.dbo.lds_User_log  T2
            ON T1.user_log_id = T2.user_log_id
WHERE T1.event_date between '2018-05-18 00:00:01' AND '2018-05-18 23:00:01' 
GROUP BY T1.user_log_id, T1.[user];

If variables can't be used in the same select you can't tackle it this way:

SELECT  T1.user_log_id,
        T1.[user], 
        T1.MinEventDate,
        T2.MaxEventDate,
       DATEDIFF(hour, T1.MinEventDate, T2.MaxEventDate) AS DaysDiff
FROM    (SELECT user_log_id,[user],min(event_date) as MinEventDate 
            FROM leads.dbo.lds_User_log
            WHERE event_date between '2018-05-18 00:00:01' AND '2018-05-18 23:00:01' 
            GROUP BY user_log_id,[user]) T1
        JOIN (SELECT user_log_id,[user],max(event_date) as MaxEventDate 
            FROM leads.dbo.lds_User_log
            WHERE event_date between '2018-05-18 00:00:01' AND '2018-05-18 23:00:01' 
            GROUP BY user_log_id,[user]) T2
            ON T1.user_log_id = T2.user_log_id
GROUP BY T1.user_log_id, T1.[user];

Hope this helps!

Upvotes: 2

Related Questions