Good afternoon/evening everyone, I have written a query, the script is below, I can't figure out how to finalize it. I would be most grateful for any help as I am a complete newbie at this. (Suitable forums have not found)
The script is necessary for further visualization, the graph should display the time of coming to work and leaving work of a particular employee for a month (I am writing for general understanding).
I need to add the following:
Arrivals field that shows the lowest time for each user for the day with the condition that Side field = "Entry". Also a Departures field that shows the maximum time for each user for the day with the condition that the Side field = "Exit". But there is a nuance, in case the user's Source = "Telegram". then he basically has only Arrivals recorded, as in the previous situation, and the Departures field should be calculated as Arrivals + 10 hours).
SELECT
d.id,
d.ufUser,
toDate(ufDate, 'Asia/Almaty') AS DAYS,
dateName('hour', ufDate, 'Asia/Almaty') AS HOURS,
arrayStringConcat(mapValues(d.ufSide), ', ') as Side,
arrayStringConcat(mapValues(d.ufOffice), ', ') as Office,
arrayStringConcat(mapValues(d.ufSource), ', ') as Source,
formatDateTime(toTime(ufDate, 'Asia/Almaty'), '%T') as Time,
User.LAST_NAME || ' ' || User.NAME || ' ' || User.SECOND_NAME as user_name
FROM db.Dom d FINAL
LEFT JOIN
(SELECT
ID,
LAST_NAME,
NAME,
SECOND_NAME
FROM db.user FINAL
) AS User ON d.ufUser = User.ID
WHERE d.ufUser != 0 AND isNotNull(user_name)
