How to show the last record of each customer for each month?

Please help, I have a table that contains the history of changes of the customer`s record for the last 3 years. And I need to output the status or record of each customer for the "last day" of "each" month.

The table looks like this:

Table A:

| ID  | Name | Number|from_date(in Timestamp)|to_date(in Timestamp)|
|:--- |:----:|:-----:|----------------------:|--------------------:|
|123  | John | 101   |20210101 01:11:15      |20210103 01:11:15    | 
|123  | John | 102   |20210103 01:11:15      |20210301 01:11:15    | 
|123  | John | 103   |20210301 01:11:15      |20210325 01:11:15    | 
|123  | John | 104   |20210325 01:11:15      |20210415 01:11:15    | 
|123  | John | 105   |20210415 01:11:15      |20210416 01:11:15    | 
|123  | John | 106   |20210416 01:11:15      |20210525 01:11:15    |
|123  | John | 104   |20210525 01:11:15      |20210915 01:11:15    | 
|123  | John | 105   |20210915 01:11:15      |null                 |

Given the data above, unfortunately there is no record for month of Feb, June, July, August and September but I need to show the data of the customers in "each" month (January to Decemeber).

The expected output should look like this:

| ID  | Name | Number|Date    |
|123  | John | 102   |20210131|
|123  | John | 102   |20210228|
|123  | John | 104   |20210331|
|123  | John | 106   |20210430|
|123  | John | 104   |20210531|
|123  | John | 104   |20210630|
|123  | John | 104   |20210731|
|123  | John | 104   |20210831|
|123  | John | 104   |20210931|

I can get the last day records of all the months which are visible in from_date column through the sql below. but for the months which are not listed or months which are in between the from_date and to_date column, I am struggling to show it.

select a.id, a.name, a.number, last_day(a.from_date) Date
from (select a.*, row_number() over (partition by a.id, trunc(from_date, 'MON') 
order by from_date desc) as seqnum from tableA a
 ) a where seqnum = 1;

for your reference, above sql output looks like this:

| ID  | Name | Number|Date    |
|123  | John | 102   |20210131|
|123  | John | 104   |20210331|
|123  | John | 106   |20210430|
|123  | John | 106   |20210531|
|123  | John | 106   |20210931|

Solution

You can use Teradata's EXPAND ON clause. To do this, we create a PERIOD data type from your from_date and to_date and then use EXPAND ON to break that period into MONTH_END chunks.

This will look something like:

 SELECT yourtable.*, BEGIN(bg) 
 FROM yourtable
 EXPAND ON PERIOD(from_date, NEXT(to_date)) AS by BY ANCHOR MONTH_END;

May have to monkey with the syntax a bit there, but it should get you in the ballpark.