Search code examples
sqlsql-serverselectmaxmin

Select min (datetime) and max(datetime)

My data is like this

| id. | date. | datetime. |
|:---- |:------:| -----:|
| 123  |  2022-01-02  | 2022-01-02 8:00:00 |
| 123  |  2022-01-02  | 2022-01-02 10:00:00 |
| 123  |  2022-01-03  | 2022-01-03 2:00:00 |
| 123  |  2022-01-03  | 2022-01-03 8:30:00 |
| 123  |  2022-01-03  | 2022-01-03 17:30:00 |

I want to select and order my data like this: (I want my result like this)

| id.  | date.  | in.   | out. |
|:---- |:------:| -----:| ---:|
| 123  | 2022-01-02| 2022-01-02 8:00:00 | 2022-01-03 2:00:00|
| 123  | 2022-01-03| 2022-01-03 8:30:00 | 2022-01-03 17:30:00|

I have been using like this

SELECT 
  [person_id] AS 'id.'
  , [date] AS 'date.'
  , MIN(datetime) AS 'in.' 
  , MAX(datetime) AS 'out.'
 FROM
tablename
GROUP BY
[person_id]
, [date]

Normal result: (I don't want result like this)

| id.  | date.  | in.   | out. |
|:---- |:------:| -----:| ---:|
| 123  | 2022-01-02| 2022-01-02 8:00:00 | 2022-01-03 10:00:00|
| 123  | 2022-01-03| 2022-01-03 02:00:00 | 2022-01-03 17:30:00|

to get my results but failed because using max(datetime) function was select the max datetime same date only but I want to select my max datetime less than 05:00:00. Please help me.

Solution

  • So I've recreated your table in MS SQL SERVER and managed to get the result you want, see image, with the following query.

    I've joined the table on itself. The first "main" table is used to show the result grouped by id and date. The second version of this table, the "sub" one, returns all the datetime records where the datetime is 5 to 29 hours more then the beginning of the "main" date that is shown. So then you only select the min and max one from the second version and there you go!

    enter image description here

    SELECT
    main.id AS 'ID'
    , main.[date] AS 'DATE'
    , MIN(sub.[datetime]) AS 'IN'
    , MAX(sub.[datetime]) AS 'OUT'
FROM
    worktime main
    LEFT JOIN worktime sub 
        on main.id = sub.id
        AND sub.[datetime] > DATEADD(hh, 5, CAST(main.[date] as datetime))
        AND sub.[datetime] < DATEADD(hh,29, CAST(main.[date] as datetime))
GROUP BY
    main.id
    , main.[date]