CODEWITHSUNDEEP
sql-serverdate
honey bee
honey bee

Reputation: 41

Find min and max data from multiple columns

I have a table that specifies exactly what date and time each employee was in a particular office.I want to know on what date and time each employee arrived at work and on what date and time he left work. I also want to know how many times he has been at work in each time process.

EmployeeTable looks like this:

id EmployeeID DateP TimeP
1 11111 1397/01/02 01:30
2 11111 1398/05/09 05:30
3 11111 1398/06/07 05:10
4 22222 1398/08/09 06:12
5 22222 1399/02/01 07:15
6 11111 1399/07/02 08:51
7 11111 1399/08/06 12:20
8 33333 1399/09/04 20:01
9 33333 1399/12/08 22:05
10 33333 1400/01/01 23:11
11 33333 1400/02/05 14:10
12 22222 1400/04/05 16:25

I want exactly select Min and Max date and time for each Employee when present in a office and how many times he/she has been present at work in each stage.:

id EmployeeID MinDateP TimeMinDateP MaxDateP TimeMaxDateP Count
1 11111 1397/01/02 01:30 1398/06/07 05:10 3
2 22222 1398/08/09 06:12 1399/02/01 07:15 2
3 11111 1399/07/02 08:51 1399/08/06 12:20 2
4 33333 1399/09/04 20:01 1400/02/05 14:10 4
5 22222 1400/04/05 16:25 1400/04/05 16:25 1

This is the SQL code that indicates exactly when each employee arrived at work and when he or she left work :

WITH cte AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY DateP, TimeP) rn1,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY DateP, TimeP) rn2
    FROM 
        EmployeeTable
),
cte2 AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                           ORDER BY DateP, TimeP) rn_first,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                           ORDER BY DateP DESC, TimeP DESC) rn_last
    FROM 
        cte
)
SELECT
    EmployeeID,
    MAX(CASE WHEN rn_first = 1 THEN DateP END) AS MinDateP,
    MAX(CASE WHEN rn_first = 1 THEN TimeP END) AS TimeMinDateP,
    MAX(CASE WHEN rn_last = 1  THEN DateP END) AS MaxDateP,
    MAX(CASE WHEN rn_last = 1  THEN TimeP END ) AS TimeMaxDateP
FROM 
    cte2
GROUP BY
    EmployeeID,
    rn1 - rn2
ORDER BY
    MIN(DateP), MIN(TimeP);

But I want to know how many times each employee has been at work at any given time (Count column)

Can anybody help me?

Upvotes: 0

Views: 186

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

If I understand correctly, you just want to know the number of records in each employee island. In that case, just select COUNT(*) in your final query:

SELECT
    EmployeeID,
    MAX(CASE WHEN rn_first = 1 THEN DateP END) AS MinDateP,
    MAX(CASE WHEN rn_first = 1 THEN TimeP END) AS TimeMinDateP,
    MAX(CASE WHEN rn_last = 1  THEN DateP END) AS MaxDateP,
    MAX(CASE WHEN rn_last = 1  THEN TimeP END) AS TimeMaxDateP,
    COUNT(*) AS count
FROM cte2
GROUP BY
    EmployeeID,
    rn1 - rn2
ORDER BY
    MIN(DateP),
    MIN(TimeP);

Upvotes: 2

Related Questions