0

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|
JNevill
  • 46,980
  • 4
  • 38
  • 63
Jojo10478
  • 29
  • 6

2 Answers2

1

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63
0

TD 16.20+ supports so-called time series aggregation. This should return your expected result:

select id, name
  ,last(number)
  ,cast(end($TD_TIMECODE_RANGE) as date)
from table1
group by time(CAL_MONTHS(1) and id, name)
         using timecode (from_date) fill (previous)
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Hi, this looks interesting! but I get an error: `No supported Time Series Aggregate function specified with the GROUP BY TIME clause.` do you know what might be the cause of it? – Jojo10478 Sep 09 '21 at 08:13
  • Hard to tell without knowing the actual SQL. Time Series don't support all standard aggregate functions: https://docs.teradata.com/r/mZqhP1sCTB4T74FbSzm7vA/lfWj_KMVYSDouOEUaoa1Hw – dnoeth Sep 09 '21 at 08:35
  • I will try to do some more testing if I can use the time series aggregation on my case. Thank you for the idea! – Jojo10478 Sep 09 '21 at 12:04
  • Hello, I have searched about Time series aggregate function and it looks like I have to convert my table into temporal table but I might be wrong. Do you have a sample table (table definition) where I can use the above sql code? – Jojo10478 Sep 28 '21 at 09:30
  • You don't need a Time Series Table, `using timecode (from_date)` enables the usage of Time Series functions. – dnoeth Sep 28 '21 at 11:09