CODEWITHSUNDEEP
sqlsql-server
Abdulquadir Shaikh
Abdulquadir Shaikh

Reputation: 105

Filter Column with separate condition in SQL

I am trying to find login and logout time from a table. I am able to figure out login and logout time.

You can see below link for that. Login Logout Time in SQL Server

The solution for this is

    SELECT DISTINCT AGENTID, CAST(EVENTDATETIME AS date) AS [Date], 
MIN([EVENTDATETIME]) OVER (PARTITION BY [AGENTID], CAST(EVENTDATETIME AS date) ORDER BY [EVENTTYPE]) AS first_login, 
MAX([EVENTDATETIME]) OVER (PARTITION BY [AGENTID], CAST(EVENTDATETIME AS date) ORDER BY [EVENTTYPE] DESC) AS last_logout,
FROM #temp

Now here what i suppose to do this filter column first_login by EVENTTYPE = 1 and last_logout by EVENTTYPE = 1.

Can you suggest a way to do that.

Upvotes: 0

Views: 58

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a case expression in MIN and MAX window functions. 

SELECT DISTINCT 
AGENTID, 
CAST(EVENTDATETIME AS date) AS [Date], 
MIN(CASE WHEN EVENTTYPE = 1 THEN [EVENTDATETIME] END) OVER(PARTITION BY [AGENTID], CAST(EVENTDATETIME AS date)) AS first_login, 
MAX(CASE WHEN EVENTTYPE = 1 THEN [EVENTDATETIME] END) OVER(PARTITION BY [AGENTID], CAST(EVENTDATETIME AS date)) AS last_logout
FROM #temp

Upvotes: 1

Related Questions