How to deal with extract(month) when there is a duplication in the date

Database
New Member

How to deal with extract(month) when there is a duplication in the date

Hi There ,

 

I am trying to use the row_number of a flag(BALANCE_CARIED_FWD) and minus the month of PAYT_DUE_DATE(which i converted in to 1-12 Jan - Dec). My issue is in the data two records(9,10) came in the same month which is breaking my logic.

Any sugestions on how to deal with this ? Thanks In Advance

 

**Note: I have converted PAYT_DUE_DATE( AUG 2016 to JULY 2017 12 months ) to PAYT_DUE_DATE_NEW ( JAN to DEC 2016 12 months) for geting the sequential months so i can minus with the ROW_NUMBER

 

sel BALANCE_CARIED_FWD,PAY_DUE_DATE, PAY_DUE_DATE_NEW,row_number() over (partition by BALANCE_CARIED_FWD order by PAY_DUE_DATE_NEW),EXTRACT(month from PAY_DUE_DATE_NEW),
(EXTRACT(month from PAY_DUE_DATE_NEW) - (row_number() over (partition by BALANCE_CARIED_FWD order by PAY_DUE_DATE_NEW))
) as grp
from MS_DATE_CONV

 

 

 

 

data_i.png

1 REPLY
Teradata Employee

Re: How to deal with extract(month) when there is a duplication in the date

Hi,

 

You should use DENSE_RANK instead of ROW_NUMBER.