1

I guess it is really hard to be friends with sql. Please help!
I have history table that contains master records of customer for 3 years. the table looks like this:

Table A:

| ID  | Name | Number|Date(in Timestamp)|
|:--- |:----:|:-----:|-----------------:|
|123  | John | 101   |20210101 01:11:15 |
|123  | John | 102   |20210103 01:11:15 |
|123  | John | 103   |20210301 01:11:15 |
|123  | John | 104   |20210325 01:11:15 |
|123  | John | 105   |20210415 01:11:15 |
|123  | John | 106   |20210416 01:11:15 |
|124  | Mary | 201   |20210101 01:11:15 |
|124  | Mary | 202   |20210103 01:11:15 |
|124  | Mary | 203   |20210201 01:11:15 |
|124  | Mary | 204   |20210225 01:11:15 |
|124  | Mary | 204   |20210315 01:11:15 |
|124  | Mary | 205   |20210416 01:11:15 |

I need to know what are the records of each customers at the end of each month.

Expected result is:

| ID  | Name | Number|Date    |
|123  | John | 102   |20210131|
|123  | John | 104   |20210331|
|123  | John | 106   |20210430|
|124  | Mary | 202   |20210131|
|124  | Mary | 204   |20210228|
|124  | Mary | 204   |20210331|
|124  | Mary | 205   |20210430|

I created below sql but it looks like i cannot use substr for Timestamp. Is there any other way to do this? will really appreciate any help!

select * from (select t1.id, t1.name, t1.number, t1.date,
ROW_NUMBER() over (partition by substr(date,1,6) order by date desc) as Rowrank
from tableA t1)sub where Rowrank = 1
Jojo10478
  • 29
  • 6

2 Answers2

3

In Teradata, you might find trunc() to be a simple method:

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

Teradata also supports qualify:

select a.id, a.name, a.number, a.date
from tableA a
qualify row_number() over (partition by trunc(date, 'MON') order by date desc) = 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    You can also get rid of the subquery in Teradata with the `QUALIFY` clause `select * from tableA QUALIFY row_number() over (partition by trunc(date, 'MON') order by "date" desc) = 1;` – JNevill Sep 03 '21 at 13:45
  • Omg this is what I am looking for! I just had to add the "a.id" and trunc(date, 'YEAR') in the partition and voila! Now I can sleep well. Thank you very much! – Jojo10478 Sep 03 '21 at 15:40
  • Hi @JNevill , I tried to use the QUALIFY and it gave me the same results! I think I`ll be using it instead of the subquery. Thank you! – Jojo10478 Sep 03 '21 at 15:41
  • 1
    @Jojo10478: No need for adding `trunc(date, 'YEAR')`, MON truncates to the first of month. Btw, `last_day(date)` can also be used. – dnoeth Sep 06 '21 at 07:35
  • @dnoeth, you are right! the query gave me the same output without the `trunc(date, 'YEAR')`. About the `last_day(date)`, yes, I just discovered that I can use it to output the date for end of month. Thanks for your comment! – Jojo10478 Sep 07 '21 at 12:45
  • Just when I thought I am already done with this problem, to my surprise I got additional question related to this topic. If you have some time, could you take a look on my latest post? I would really appreciate any help! – Jojo10478 Sep 07 '21 at 14:18
  • @Jojo10478 . . . If you have a new question, it should be asked as a *new* question. – Gordon Linoff Sep 07 '21 at 15:42
1

Try this answer:

Get the Year and Month values from the timestamp value and partition based on the Id, year and Month.

select * 
from (
    select t1.id, t1.name, t1.number, t1.date,
    ROW_NUMBER() over (partition by t1.id,YEAR(date_format(str_to_date(t1.date, '%Y%m%d'),'%Y-%m-%d')),MONTH(date_format(str_to_date(t1.date, '%Y%m%d'),'%Y-%m-%d')) order by date desc) as Rowrank
    from tableA t1
     )sub where Rowrank = 1
DineshDB
  • 5,998
  • 7
  • 33
  • 49
  • I am having syntax error between "," and "YEAR". for the line "ROW_NUMBER() over (partition by t1.id,YEAR". I am afraid this syntax or format is not available for Teradata database. – Jojo10478 Sep 03 '21 at 07:55
  • 1
    Previously the tag was `mysql` and now you changed it to teradata. Please try to do the same pattern in teradata like convert the timestamp value to date and fetch `month` and `year`, then partition using the columns – DineshDB Sep 03 '21 at 08:11
  • yes, I am sorry that was my bad. I honestly just realized that mysql is another system. I will try to look for the same pattern in teradata. thanks so much! – Jojo10478 Sep 03 '21 at 08:31
  • This pattern gave me expected results in teradata. Thanks again for the idea! – Jojo10478 Sep 03 '21 at 15:18