Check if date is equal to last day of its respective quarter

Question

I've a column with dates and want to introduce another column which is a flag (1/0) about whether the date is the last day of its respective quarter or not.

Data:

| date       |
|------------|
| 2021-04-10 |
| 2021-05-19 |
| 2021-09-30 |

Expected output:

| date       | is_last_day_of_quarter |
|------------|------------------------|
| 2021-04-10 | 0                      |
| 2021-05-19 | 0                      |
| 2021-09-30 | 1                      |

---

I was able to come up with the below, which seems to work:

select
    date,
    case
        when date = last_day_of_q then 1 else 0
    end as is_last_day_of_quarter

from(
    select
        date,
        case
            when month(date) < 4 then (concat(year(date), '03-31'))
            when month(date) between 4 and 6 then concat((year(date)), '-06-30')
            when month(date) between 7 and 9 then concat((year(date)), '-09-30')
            when month(date) between 10 and 12 then concat((year(date)), '-12-31')
        end as last_day_of_q
        from
            some_table
    ) t

I was wondering if maybe there's a better way to do this or if it can be achieved without having to use a subquery?

Answer 1

Method using calculation instead of CASE:

with mytable as(
select '2021-04-10' as mydate union all
select '2021-05-19' as mydate union all
select '2021-09-30' as mydate
)
select mydate, 
       date_sub( concat(YEAR(mydate),'-',lpad(((INT((MONTH(mydate)-1)/3)+1)*3)+1,2,0),'-01'),1) qtr_last_date,
       case when mydate = date_sub( concat(YEAR(mydate),'-',lpad(((INT((MONTH(mydate)-1)/3)+1)*3)+1,2,0),'-01'),1) then 1 else 0 end as flag
 from mytable

Result:

  mydate         qtr_last_date  flag    
  2021-04-10     2021-06-30      0
  2021-05-19     2021-06-30      0
  2021-09-30     2021-09-30      1

But I like your method of quarter last day calculation (CASE expression in question) because it is much easier to read and understand. And you can easily remove subquery:

case
    when date = case
                   when month(date) < 4 then (concat(year(date), '03-31'))
                   when month(date) between 4 and 6 then concat((year(date)), '-06-30')
                   when month(date) between 7 and 9 then concat((year(date)), '-09-30')
                   when month(date) between 10 and 12 then concat((year(date)), '-12-31')
                 end
    then 1 else 0
end as is_last_day_of_quarter