sqlexasol

How to get the last day of the month without LAST_DAY() or EOMONTH()?

I have a table t with:

DATE LOCATION PRODUCT_ID AMOUNT
2021-10-29 1 123 10
2021-10-30 1 123 9
2021-10-31 1 123 8
2021-10-29 1 456 100
2021-10-30 1 456 90
2021-10-31 1 456 80
2021-10-29 2 123 18
2021-10-30 2 123 17
2021-11-29 2 456 18

I need to find the AMOUNT of each PRODUCT_ID for each combination of LOCATION + PRODUCT_ID.

If a PRODUCT_ID has no entry for that day the AMOUNT is NULL.

So the result should look like:

DATE LOCATION PRODUCT_ID AMOUNT
2021-10-31 1 123 8
2021-10-31 1 456 80
2021-10-31 2 123 NULL
2021-11-30 2 456 NULL

Sadly EXASOL has no LAST_DAY() or EOMONTH() function. How can I solve this?

Solution

  • You can get to the last day of the month using a date_trunc function in combination with date_add:

    case
    when t.date = date_add('day', -1, date_add('month', 1, date_trunc('month', t.date)))
    then 'Y' else 'N' end as end_of_month

    That being said, if you group your table for all combinations of locations and products, you will not get NULLs for products without sales on the last day of the month as shown in your output table.

    When you group your data, any value that does not exist will simply not show up in your output table. If you want to force nulls to show up, you can create a new table that contains all combinations of products, locations, and hard-coded end of month dates.

    Then, you can left join your old table with this new hard-coded table by date, location, and product. This method will give you the NULL values you expect.