MySQL determine day of week when working with multiple date fields

MySQL

So I have this existing case statement in my query:

SELECT
CASE
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

CASE
WHEN mc1.day IS NOT NULL
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

Best Answer

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:

CASE
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.

Related Solutions

Mysql – Get all items from list table which don’t have a successful log entry for ‘sent’ in log table

Query itself seems to be quite straightforward (from data sample provided I assume that a record with action:sent , failure_reason:NULL is the final state for a given list_id) :

SELECT a.*
FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

However, if tables are huge , performance will suffer even if you have a covering index in log table which is probably not good - name "log" implies that you are mostly inserting data into it (and insert has to be as fast as possible), so each extra index defeats the main purpose of this table.
It looks to me though that date/datetime field is missed in the model; I guess you typically want failed tasks for a particular (presumably relatively small) date interval .

MySQL: LEFT OUTER JOIN within reason

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.

Best Answer

Related Solutions

Mysql – Get all items from list table which don’t have a successful log entry for ‘sent’ in log table

MySQL: LEFT OUTER JOIN within reason

Related Question