CODEWITHSUNDEEP
sqlsql-server
GGw
GGw

Reputation: 443

SQL Server group by with date differences

I have this line of codes

select min(A.id) 'id',
      CONVERT (VARCHAR(10),min(A.date_in),101) as 'Date_in',
      CONVERT (VARCHAR(10),min(A.date_in),108) as 'Time_in',
      CONVERT (VARCHAR(10),min(A.date_out),101) as 'Date_out',
      CONVERT (VARCHAR(10),min(A.date_out),108) as 'Time_out',
      min(B.firstname) 'firstname',min(B.lastname) 'lastname', 
      min(A.ip_address) 'ip_address',
      CASE min(B.UserType)
        WHEN 1029 THEN 'Administator'
        WHEN 1030 THEN 'Casheiring'
        WHEN 1031 THEN 'Front Office'
        WHEN 1032 THEN 'Housekeeping'
        WHEN 1033 THEN 'POS'
END as 'user_type'
from AEmployeeLogs A 
LEFT JOIN AUsers B on B.id = A.[user_id]
group by A.[user_id]
ORDER BY min(A.id) DESC

In this query i get this:

 id   Date_in     Time_In   Date Out     Time_Out   firstname  lastname  ip_address      user_type
 5    10/05/2017  15:14:59  10/05/2017   20:20:37   LeBron     James     192.168.0.1     Administrator
 1    10/04/2017  12:04:32  10/05/2017   16:02:53   Kevin      Durant    192.168.0.2     Administrator

These will get me the 2 ids that i grouped by I want that in every date should be there every time I wanted it to be like this:

  id   Date_in     Time_In   Date Out     Time_Out   firstname  lastname  ip_address      user_type
  5    10/05/2017  15:14:59  10/05/2017   20:20:37   LeBron     James     192.168.0.1     Administrator
  3    10/05/2017  11:58:20  10/05/2017   16:02:53   Kevin      Durant    192.168.0.2     Administrator
  1    10/04/2017  12:04:32  10/05/2017   16:02:53   Kevin      Durant    192.168.0.2     Administrator

It should have this output I just wanted to get min() in every date not the min() of the whole table even with the group by clause please help me and thank you :)

Upvotes: 0

Views: 41

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

I think you want something like this.

select *
from
(
    select A.id,
        CONVERT (VARCHAR(10), A.date_in,101) as 'Date_in',
        CONVERT (VARCHAR(10), A.date_in,108) as 'Time_in',
        CONVERT (VARCHAR(10), A.date_out,101) as 'Date_out',
        CONVERT (VARCHAR(10), A.date_out,108) as 'Time_out',
        B.firstname,
        B.lastname,
        A.ip_address,
        CASE B.UserType
            WHEN 1029 THEN 'Administator'
            WHEN 1030 THEN 'Casheiring'
            WHEN 1031 THEN 'Front Office'
            WHEN 1032 THEN 'Housekeeping'
            WHEN 1033 THEN 'POS'
        END as 'user_type'
        , RowNum = ROW_NUMBER() over(partition by A.id, A.date_in order by A.date_in)
    from AEmployeeLogs A 
    LEFT JOIN AUsers B on B.id = A.[user_id]
) x
where x.RowNum = 1
ORDER BY x.id DESC

Also, you should use better aliases than A, B, C, etc. That is a bad habit to get into.

Upvotes: 1

Related Questions