Best Of

@damen

the learning lesson is you shouldn't have nested CASE statements

AVG(
case 
when (CASE
WHEN (`instrument` = 'first home') THEN '1st Home' 
WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'
WHEN (`instrument` = 'fha streamline') THEN 'FSR' 
WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 
WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 
WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 
WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 
WHEN (`instrument` = 'fsr') THEN 'FSR' 
WHEN (`instrument` = 'home again') THEN 'Home Again' 
WHEN (`instrument` = 'NoMI') THEN 'NoMi'
WHEN (`instrument` = 'score') THEN 'Score' 
ELSE `instrument` 
END) = '1st Home' then `spread` end) 

because if you boil it down.

your inner CASE statement will return a number.

so then in your outer CASE statement you have

CASE when <number> = '1st Home' then spread end.

well... it'll never = '1st Home'. so you always return NULL because you don't have an ELSE clause in place.

@ST_-Superman-_ and @datamatt , typically for anything like headcount data, you'd want data the granularity of one row per client_id per facility per day.

if you don't, imagine i have a customer who was admitted last month and stays for 3 months. in my report, i would only show them on the first day they were admitted.

you are correct it would lead to row growth to have one row per user per day, but it is definitely necessary.

to prevent the "not showing all rows" warning you need to make sure you have a metric / aggregate function. I usually add something like sum(1) as to give me rowcount, or you could add a binary column to the dataset.

my approach would be to try to take a timestamp and date_format( hh:mm) that would always round down.

to address round up, i'd use a CASE statement so if second() is 30 then hh:mm + 1 minute.