我想在一个季度的最后一天增加28天。
我目前有以下内容来确定季度期间,然后我如何在该季度最后一个月的最后一天加上28天。
CASE -- Results: 2011-Q4 (Financial Year-Qtr)
WHEN MONTH(T5.date_purchased) BETWEEN 1 AND 3 THEN concat((YEAR(T5.date_purchased) - 1), '-', 'Q3')
WHEN MONTH(T5.date_purchased) BETWEEN 4 AND 6 THEN concat((YEAR(T5.date_purchased) - 1), '-', 'Q4')
WHEN MONTH(T5.date_purchased) BETWEEN 7 AND 9 THEN concat((YEAR(T5.date_purchased) - 0), '-', 'Q1')
WHEN MONTH(T5.date_purchased) BETWEEN 10 AND 12 THEN concat((YEAR(T5.date_purchased) - 0), '-', 'Q2')
END AS FYrQtr,
我不需要解释任何异常情况,只需在下一季度的下个月28日。
我尝试过以下方法,但当然,这会使订单日期增加28天,而不是财务qtr。(我不需要时间)
CASE -- Results: 2012-06-20 07:20:07 not 2012-07-28
WHEN MONTH(T5.date_purchased) BETWEEN 1 AND 3 THEN date_add(T5.date_purchased, Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 4 AND 6 THEN date_add(T5.date_purchased, Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 7 AND 9 THEN date_add(T5.date_purchased, Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 10 AND 12 THEN date_add(T5.date_purchased, Interval 28 day)
END AS FRangeQtrBAS,
非常感谢。
从采购日期获取年份,并将其与已知的季度末月份和日期连接起来:
CASE
WHEN MONTH(T5.date_purchased) BETWEEN 1 AND 3 THEN date_add(CONCAT(YEAR(T5.date_purchased), '-03-31'), Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 4 AND 6 THEN date_add(CONCAT(YEAR(T5.date_purchased), '-06-30'), Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 7 AND 9 THEN date_add(CONCAT(YEAR(T5.date_purchased), '-09-30'), Interval 28 day)
WHEN MONTH(T5.date_purchased) BETWEEN 10 AND 12 THEN date_add(CONCAT(YEAR(T5.date_purchased), '-12-31'), Interval 28 day)
END AS FRangeQtrBAS