Beastmode to get first week of month

ColinHaze

Hi,

I am currently trying to get a field that displays the value for the first week of the month.

Currently I have it written as

(CASE
WHEN
WEEK(DATE_SUB(CURDATE(), INTERVAL 2 WEEK)) = WEEK(ORDERSTARTDATE_DATE)
THEN (carrier pay)
END)
/
(CASE
WHEN
WEEK(DATE_SUB(CURDATE(), INTERVAL 2 WEEK)) = WEEK(ORDERSTARTDATE_DATE)
THEN (Loads)
END)

However, I know next week ill have to change the interval to 3 in order to get the first week of the month.

Any advice on how to make this beastmode work better?

MarkSnodgrass

I would dynamically determine the first day of the month by doing this:

1. DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)

and then wrap the week function around this to figure out the week number. This would give you the first week of the month.

MarkSnodgrass

You would want to remove CURDATE() from your formula and base it off of your orderstartdate. Something like this:

(CASE WHEN WEEK(DATE_SUB(ORDERSTARTDATE_DATE, INTERVAL (DAYOFMONTH(ORDERSTARTDATE_DATE) - 1) DAY)) = WEEK(ORDERSTARTDATE_DATE)
THEN (carrier pay)END)
/
(CASE WHEN WEEK(DATE_SUB(ORDERSTARTDATE_DATE, INTERVAL (DAYOFMONTH(ORDERSTARTDATE_DATE) - 1) DAY)) = WEEK(ORDERSTARTDATE_DATE)
THEN (Loads)
END)

MarkSnodgrass

I would dynamically determine the first day of the month by doing this:

1. DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)

and then wrap the week function around this to figure out the week number. This would give you the first week of the month.

ColinHaze

Worked perfectly, thanks again Mark.

ColinHaze

Hey Mark,

Another quick question. I am unable to change the range of the date field to the prior month. Would I need to do another date_sub and interval it by one month?

MarkSnodgrass

You would want to remove CURDATE() from your formula and base it off of your orderstartdate. Something like this:

(CASE WHEN WEEK(DATE_SUB(ORDERSTARTDATE_DATE, INTERVAL (DAYOFMONTH(ORDERSTARTDATE_DATE) - 1) DAY)) = WEEK(ORDERSTARTDATE_DATE)
THEN (carrier pay)END)
/
(CASE WHEN WEEK(DATE_SUB(ORDERSTARTDATE_DATE, INTERVAL (DAYOFMONTH(ORDERSTARTDATE_DATE) - 1) DAY)) = WEEK(ORDERSTARTDATE_DATE)
THEN (Loads)
END)

ColinHaze

That was my original thought as well but I noticed our first order of SEP was on the 5th so I had to switch the -1 around to - 3. That seems to work.