MAX datetime GROUP BY in Query JSON with SQL

Queries and Resources
1

Here is the original query

select ae.kronos_id, ae.first_name, ae.last_name, MAX(wc.start_time, DATE) as max_start_time
from {{qry_Active_Employees.data}} as ae INNER JOIN {{qry_Whos_Clocked_In.data}} as wc
ON ae.kronos_id = wc.account_id
GROUP by ae.kronos_id, ae.first_name, ae.last_name
order by ae.kronos_id

It returns the correct wc.start_time value when there is only one start_time. If there are multiple start_time(s), it return "undefined".

Assuming the problem was with the start_time being a string, I tried the following.

MAX(CAST(ae.start_time) as DATETIME) which returns "undefined" for any multiple start_time(s).

MAX(CONVERT(ae.start_time, DATETIME)) which returns no rows at all.

Thanks

2

Answered my own question.

I added a transformer to the original query and created DATE versions of the fields along with the string ones.

sets.time_entries.map( (times, index) => {
	let transaction = {}
transaction.account_id = sets.employee.account_id
transaction.str_start_time = times.start_time;
transaction.date_start_time = Date.parse(times.start_time);
transaction.str_end_time = times.end_time;
transaction.date_end_time = Date.parse(times.end_time);
	result.push(transaction)

})
})