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

Question

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?

Is building the date as the (1st of (month+1) )- 1 day possible? — Nathan_Sav, May 06 '22 at 13:03
Yes but building a list of last day of month table via CTE yields me the first 2 rows from my exptected result but I'm still missing the last 2 rows with the NULL value. — Vega, May 06 '22 at 13:52

score 0 · Answer 1 · answered May 06 '22 at 14:14

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.

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

1 Answers1