CODEWITHSUNDEEP
sqldatabase
pebkac
pebkac

Reputation: 21

I have a table and I need time difference between two events using SQL Query

Here's the table that I have,

Number Status Time
2132 Group Joined August 22, 2022, 2:54 PM
3299 Group Joined August 12, 2022, 9:24 PM
3299 Group Left August 23, 2022, 5:43 PM
2132 Group Left August 30, 2022, 5:32 PM
2132 Group Joined September 3, 2022, 2:23 PM

and I need time difference between Group Joined and Group Left of same Number and the Time column in the table is in bona fide datetime format.

And I only need the phone numbers whose time period is More than 12 hours.

I have tried using DATEDIFF but I am somehow not able to get the output I need.

Upvotes: 0

Views: 44

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

We can use DATEDIFF() along with conditional aggregation:

SELECT Number
FROM yourTable
GROUP BY Number
HAVING DATEDIFF(hour,
                MAX(CASE WHEN Status = 'Group Joined' THEN Time END),
                MAX(CASE WHEN Status = 'Group Left'   THEN Time END)) > 12;

Here the max of CASE expressions find the times for group joined and group left.

Upvotes: 1

Related Questions