CODEWITHSUNDEEP
sqldateamazon-redshift
ranjan jaiswal
ranjan jaiswal

Reputation: 17

To find data from maximum date to last 12 month in SQL redshift

I have a string column(year_to_month) in the table with value as YYYYMM format. I have to find data between maximum year_to_month to last 12 months. I have tried below code, but it is not working and picking some random garbage value for the day as there is no day value present in year_to_month

  select * from Table
  where to_date between to_date(year_to_month,'mm') between max(to_date(year_to_month,'mm'))-12 and 
  max(to_date(year_to_month,'mm'))

sample data
201911
201910
201909
201812
201802
201805

I am using Amazon redshift database and running query in workbench

Can anyone please help me what am I doing wrong?

Upvotes: 0

Views: 1805

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This should be sufficient:

select *
from Table
where to_date >= to_char(year_to_month,'yyyymm')

You don't have to compare to the maximum year_to_month in the data, because you are going to get all those values anyway.

Upvotes: 1

Gen Wan
Gen Wan

Reputation: 2009

Below is my approach:

select newdate
from (select to_date(year_to_month,'yyyymm') as newdate from sample) t
where newdate
between
(
select to_date(year_to_month,'yyyymm') - interval '12' month
from
(
  select t.*, row_number() over (order by year_to_month desc) as rn
  from sample t
)
where rn = 1
)
and 
(
select to_date(year_to_month,'yyyymm') 
from
(
  select t.*, row_number() over (order by year_to_month desc) as rn
  from sample t
)
where rn = 1
)

The clarification:

1, I used the below subquery to select the max date.

select ...
from
(
  select t.*, row_number() over (order by year_to_month desc) as rn
  from sample t
)
where rn = 1

2, I used the below query to get the date before 12 months

select to_date(year_to_month,'yyyymm') - interval '12' month

Test Result (I tested it in Oracle 18c. Let me know if you meet any issue in Amazon Redshift):

DB<>Fiddle

Upvotes: 0

Related Questions