So I have this existing case statement in my query:

WHEN dl.list_id = 1000000
AND dl.date_inserted != ''
THEN dl.date_inserted
ELSE dl.entry_date
END AS date

Let me give some context here. So as soon as we have a record move to list_id 1000000 I need to reference date_inserted to determine when this record entered the table but for all else I need to reference entry_date. Next I need to be able to identify which day of the week the above date amounts to for ex. Saturday. So I created a 2nd table which lists each date for 2020 and the corresponding day so my plan is

THEN mc1.day 
ELSE mc2.day 
END AS day_of_the_week
LEFT JOIN "db"."meta_calendar" mc1 ON dl.date_inserted = mc1.date
LEFT JOIN "db"."meta_calendar" mc2 ON dl.entry_date = mc1.date AND dl.date_inserted = ''

The result seems to be producing the expected output but I have a feeling there could be a better way to achieve this. Basically I need this validated and if this is not the ideal way to achieve this then please advise


What you're doing is fine as far as a working solution goes, and helps avoid using functions to calculate the day of the week in memory every time the query runs. But MySQL has built in functions to calculate it as well which is just as fine of a solution too.

For example, the following returns the week day index using MySQL's WEEKDAY() function:

WHEN dl.date_inserted IS NOT NULL
THEN WEEKDAY(dl.date_inserted)
ELSE WEEKDAY(dl.entry_date)
END AS day_of_the_week

If you want the name of the week day, then you can use the DAYNAME() function instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.