Trying to create a sql statement in magic ETL to compare YOY: Month-to-Date (MTD) by MAX date. My current code from
https://domo-support.domo.com/s/article/360043430133?language=en_US
is:
CASE WHEN (MONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) = MONTH(CURDATE()) and YEAR(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) = YEAR(CURDATE()) and DAYOFMONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) <=DAYOFMONTH(CURDATE())) THEN 'This Year' WHEN (MONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) = MONTH(CURDATE()) AND YEAR(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 year)) and DAYOFMONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE
) <=DAYOFMONTH(CURDATE())) THEN 'Last Year' END
However since this is based on current date, there are times when there might not be a purchase on the current date so 'This Year' could pull latest date of 4/10/24 while 'Last Year' would pull current date 4/12/24 - I want to compare max dates of current period so if max date of 'This Year' is 4/10/24 then the code would classify the max date of 'Last Year' as 4/10/23.
I understand you can use the max function in dataflow, however from my research I am having a hard time creating a code to obtain my goal and am just hoping with a slight tweak of my magic etl code, I could obtain my goal. I have played with subtime function as well with this code but still learning sql so its been a struggle. Appreciate any insight and guidance!