Date_ADD / CONCAT MONTHNAME - Syntax Error

Whimzyy

Hey Everyone!

I am trying combine two different beast modes I am already using and keep running into syntax errors.

DATE_ADD(`Submit Date`,21 day) - Used to add +21 days to submit date for new date

I am wanting to add

CONCAT(MONTHNAME(`Locked`),
(CASE WHEN DAY(`Locked`) <= 15
THEN ' 1st - 15th'
ELSE ' 16th - EOM'
END)
)

to the same beast mode so the new dates are now broken down by 1st-15th and 15th-EOM.

Any help would be appreciated!

Thanks!!

Find more posts tagged with

Comments

GrantSmith

Hi @Whimzyy

Try this:

CONCAT(MONTHNAME(DATE_ADD(`Submit Date`,21 day)),
(CASE WHEN DAY(DATE_ADD(`Submit Date`,21 day)) <= 15
THEN ' 1st - 15th'
ELSE ' 16th - EOM'
END)
)

It's also helpful next time if you post your current beast mode you're having issues with to help get a better idea of what you're attempting to do and the exact syntax to help diagnose your error.

jaeW_at_Onyx

is it beating a dead horse to say that this type of calc should be executed in a date dimension?

If it were me,

1, in ETL i'd add the 'Submit Date (Date + 21 days) to the fact table.

2 create a date dimension where for each day i indicate "isFirstHalfOfMonth" as a binary. AND add the concatenation of MonthName + 'H1' or 'H2' which IMHO is a cleaner representation of 1-15 vs 16-EOM.

then JOIN the date Dimension to the fact table on Submit Date.

What will ultimately happen is people will want to change the representation of H1 and H2 OR you'll have new business rules for what happens in February. Do you really want to have to manually update / maintain all the cards using this beast mode?