I need help removing duplicate time zone records from my select query without deleting them. The current result is as follows:
Employee ID # Presence Presence Start Time Presence End Time GMT Presence Start Time691 Out Of Office 2020-02-01 04:30:00 2020-02-01 14:30:00 2020-02-01 12:30:00691 Out Of Office 2020-02-01 05:30:00 2020-02-01 15:30:00 2020-02-01 12:30:00691 Out Of Office 2020-02-01 07:30:00 2020-02-01 17:30:00 2020-02-01 12:30:00691 Out Of Office 2020-02-01 13:30:00 2020-02-01 23:30:00 2020-02-01 12:30:00691 Out Of Office 2020-02-01 20:30:00 2020-02-02 06:30:00 2020-02-01 12:30:00435 Out Of Office 2020-02-01 00:15:00 2020-02-01 09:00:00 2020-01-31 16:15:005681 Out Of Office 2020-02-02 07:00:00 2020-02-02 15:45:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 08:00:00 2020-02-02 16:45:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 10:00:00 2020-02-02 18:45:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 16:00:00 2020-02-03 00:45:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 23:00:00 2020-02-03 07:45:00 2020-02-02 15:00:001927 Out Of Office 2020-02-02 07:00:00 2020-02-02 18:15:00 2020-02-02 15:00:001927 Out Of Office 2020-02-02 08:00:00 2020-02-02 19:15:00 2020-02-02 15:00:001927 Out Of Office 2020-02-02 10:00:00 2020-02-02 21:15:00 2020-02-02 15:00:001927 Out Of Office 2020-02-02 16:00:00 2020-02-03 03:15:00 2020-02-02 15:00:001927 Out Of Office 2020-02-02 23:00:00 2020-02-03 10:15:00 2020-02-02 15:00:00The table returns duplicate GMT start times for the same employee, the database appears to be duplicating the results based on different time zones.
I just want to remove the duplicate GMT Presence Start Times
Employee ID # 691 should have 1 row, same with 5681 and 1927. Can someone please help?
Advertisement
Answer
You can use a GROUP BY clause to give unique values of GMT Presence Start Time for each employees Presence in the log. You need to use an aggregation function on the Presence Start Time and Presence End Time functions; I’ve chosen MIN in my example, but you might want something else.
SELECT [Employee ID #], [Presence], MIN([Presence Start Time]), MIN([Presence Start Time]), [GMT Presence Start Time]FROM dataGROUP BY [Employee ID #], [Presence], [GMT Presence Start Time]Output (for your sample data):
Employee ID # Presence Presence Start Time Presence Start Time GMT Presence Start Time435 Out Of Office 2020-02-01 00:15:00 2020-02-01 09:00:00 2020-01-31 16:15:00691 Out Of Office 2020-02-01 04:30:00 2020-02-01 14:30:00 2020-02-01 12:30:001927 Out Of Office 2020-02-02 07:00:00 2020-02-02 18:15:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 07:00:00 2020-02-02 15:45:00 2020-02-02 15:00:00Alternatively you can use window functions on the Presence Start and End times:
SELECT DISTINCT [Employee ID #], [Presence], FIRST_VALUE([Presence Start Time]) OVER (PARTITION BY [Employee ID #], [Presence], [GMT Presence Start Time] ORDER BY [Presence Start Time]) AS [Presence Start Time], FIRST_VALUE([Presence End Time]) OVER (PARTITION BY [Employee ID #], [Presence], [GMT Presence Start Time] ORDER BY [Presence End Time]) AS [Presence End Time], [GMT Presence Start Time]FROM dataOutput:
Employee ID # Presence Presence Start Time Presence End Time GMT Presence Start Time435 Out Of Office 2020-02-01 00:15:00 2020-02-01 09:00:00 2020-01-31 16:15:00691 Out Of Office 2020-02-01 04:30:00 2020-02-01 14:30:00 2020-02-01 12:30:001927 Out Of Office 2020-02-02 07:00:00 2020-02-02 18:15:00 2020-02-02 15:00:005681 Out Of Office 2020-02-02 07:00:00 2020-02-02 15:45:00 2020-02-02 15:00:00