0

My question is similar to the question posted here: How can I get the min and max date per date of employees?, but with one additional requirement. If the table has an additional location column such as:

Employee_Number DateTime Location
00000001 2014/01/14 09:20 Wendys
00000001 2014/01/14 12:35 McDonalds
00000001 2014/01/14 13:35 Walmart
00000002 2014/01/14 09:20 Arbys
00000001 2014/01/14 22:49 Burger King
00000001 2014/01/15 09:35 Sonic
00000001 2014/01/15 10:35 Dairy Queen
00000001 2014/01/15 17:35 Arbys
00000002 2014/01/14 12:34 McDonalds
00000002 2014/01/14 17:34 Subway

And having a SELECT statement that outputs:

Employee_Number DateTime MIN MAX MIN Location MAX Location
00000001 2014/01/14 2014/01/14 09:20 2014/01/14 22:49 Wendys Burger King
00000001 2014/01/15 2014/01/15 09:35 2014/01/15 17:35 Sonic Arbys
00000002 2014/01/14 2014/01/14 09:20 2014/01/14 17:34 Arbys Subway

Thanks for any help!

Jared.1773
  • 15
  • 3
  • tag your database – eshirvana Oct 28 '21 at 17:41
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – SMor Oct 28 '21 at 20:13
  • The duplicate shows the general technique - you need to apply the row_number logic twice to get first (ascending) and first (descending). There are, of course, other ways. – SMor Oct 28 '21 at 20:16

1 Answers1

1

If this is your table:

create table MyTable (
    Employee_Number int,
    DateTime    datetime,
    Location    varchar(20)
)

insert into MyTable values
(00000001,  '2014/01/14 09:20:00',  'Wendys'),
(00000001,  '2014/01/14 12:35:00',  'McDonalds'),
(00000001,  '2014/01/14 13:35:00',  'Walmart'),
(00000002,  '2014/01/14 09:20:00',  'Arbys'),
(00000001,  '2014/01/14 22:49:00',  'Burger King'),
(00000001,  '2014/01/15 09:35:00',  'Sonic'),
(00000001,  '2014/01/15 10:35:00',  'Dairy Queen'),
(00000001,  '2014/01/15 17:35:00',  'Arbys'),
(00000002,  '2014/01/14 12:34:00',  'McDonalds'),
(00000002,  '2014/01/14 17:34:00',  'Subway')

This would be your query:

with MyResult (Row_num_min, Row_num_max, Employee_Number, DateTime, Location)
    as
    (
      select 
        ROW_NUMBER() OVER (PARTITION BY Employee_Number, DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime)) order by DateTime) AS ROW_NUM_MIN,
        ROW_NUMBER() OVER (PARTITION BY Employee_Number, DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime)) order by DateTime DESC) AS ROW_NUM_MAX,
        Employee_Number, 
        DateTime, 
        Location
      from MyTable
    )
    select
      Employee_Number,
      DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))as DateTime,
      MAX(CASE WHEN Row_num_min = 1 THEN DateTime END) AS MIN,
      MAX(CASE WHEN Row_num_max = 1 THEN DateTime END) AS MAX,
      MAX(CASE WHEN Row_num_min = 1 THEN Location END) AS MIN_Location,
      MAX(CASE WHEN Row_num_max = 1 THEN Location END) AS MAX_Location
    from MyResult
    where row_num_min = 1 or row_num_max = 1
    group by 
      Employee_Number,
      DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))
    order by 
      Employee_Number,
      DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))

And this is the result (the same as you expected):

| Employee_Number |             DateTime |                  MIN |                  MAX | MIN_Location | MAX_Location |
|-----------------|----------------------|----------------------|----------------------|--------------|--------------|
|               1 | 2014-01-14T00:00:00Z | 2014-01-14T09:20:00Z | 2014-01-14T22:49:00Z |       Wendys |  Burger King |
|               1 | 2014-01-15T00:00:00Z | 2014-01-15T09:35:00Z | 2014-01-15T17:35:00Z |        Sonic |        Arbys |
|               2 | 2014-01-14T00:00:00Z | 2014-01-14T09:20:00Z | 2014-01-14T17:34:00Z |        Arbys |       Subway |

SQL Fiddle: http://sqlfiddle.com/#!18/0a4b74/26

Carlos
  • 1,638
  • 5
  • 21
  • 39
  • @Jared1773 if this solved your problem, do not forget to mark it as accepted answer, please (and optionally upvote it). Thank you – Carlos Nov 19 '21 at 11:34