Sql-server – Creating a Min date with Session Date also included in the table

sql server

Code: MSSQL / Bigquery

SELECT EmployeeId, SessionLoginLocal, cast(MIN(SessionLoginLocal) as date)MinSession
FROM database.data_source.user_session_table 
Group by EmployeeId, SessionLoginLocal
Order by EmployeeId

Current output:

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------    
   1 |        354 | 2017-12-12T07:29:36 | 2017-12-12     
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11     
   3 |        354 | 2017-12-12T10:59:34 | 2017-12-12     
   4 |        354 | 2017-12-11T18:32:59 | 2017-12-11     
   5 |        354 | 2017-12-11T18:33:34 | 2017-12-11     
   6 |        354 | 2017-12-11T17:16:58 | 2017-12-11     
   7 |        354 | 2017-12-12T10:56:29 | 2017-12-12     
   8 |        354 | 2017-12-11T19:37:54 | 2017-12-11     
   9 |        354 | 2017-12-12T10:57:52 | 2017-12-12

Desired Output

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------    
   1 |        354 | 2017-12-12T07:29:36 | NULL   
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11     
   3 |        354 | 2017-12-12T10:59:34 | NULL   
   4 |        354 | 2017-12-11T18:32:59 | NULL   
   5 |        354 | 2017-12-11T18:33:34 | NULL   
   6 |        354 | 2017-12-11T17:16:58 | NULL   
   7 |        354 | 2017-12-12T10:56:29 | NULL   
   8 |        354 | 2017-12-11T19:37:54 | NULL   
   9 |        354 | 2017-12-12T10:57:52 | NULL

The goal of this view is to create a stacked column graph with the date along the X axis, and the number of sessions on the Y.
Currently the number of sessions by distinct employee per day are displayed, now I am trying to include a second column with the minimum session date, basically highlighting when they had their first session. How do i create the column with the Min(SessionTime) to only include the first session and create nulls for their other sessions?

Best Answer

When you post questions like this, it's really helpful if you post your sample data as inserts, so anyone can hop in and immediately query it.

CREATE TABLE #whatever
(
    id INT,
    employeeid INT,
    sessionloginlocal DATETIME
);

INSERT #whatever ( id, employeeid, sessionloginlocal )
SELECT *
FROM (
VALUES 
(1,   354, '2017-12-12 07:29:36'),   
(2,   354, '2017-12-11 16:58:21'),   
(3,   354, '2017-12-12 10:59:34'),   
(4,   354, '2017-12-11 18:32:59'),   
(5,   354, '2017-12-11 18:33:34'),   
(6,   354, '2017-12-11 17:16:58'),   
(7,   354, '2017-12-12 10:56:29'),   
(8,   354, '2017-12-11 19:37:54'),   
(9,   354, '2017-12-12 10:57:52')
) AS x (id, employeeid, sessionloginlocal)

This code uses a case expression and MIN as a windowing function to find the min login time per user. When they match, we display it as a date. When they don't we display NULL.

SELECT *,
       CASE WHEN w.sessionloginlocal = 
               MIN(w.sessionloginlocal) OVER ( PARTITION BY w.employeeid ) 
               THEN CONVERT(DATE, w.sessionloginlocal)
               ELSE NULL
       END AS min_sessionlocal
FROM   #whatever AS w;
Related Question