Find the minimum alignment date query based on alignment number

Database
Enthusiast

Find the minimum alignment date query based on alignment number

Hi Frinds,

Need your help.

I need to find the minimum alignment date of alignment numbers.





Alignment No Alignment_Date
30 20161007
31 20161008
32 20161009
40 20161008
41 20161009
42 20161010
56 20161101
57 20161102
58 20161103

If you see from the table above, alignment no 30,31,32 which are in sequence has the minimum alignment date as 20161007, so this alignment date would be picked.

The next alignment no starts from 40,41,42. So here the alignment date that should be picking is the minimum of these sequence which is  20161008.

Again next starts from 56,57,58. So the mimun alignment date in these sequence is 20161101.

Can you guys help with the query to achive this?

Regards

SJ

1 REPLY
Teradata Employee

Re: Find the minimum alignment date query based on alignment number

drop table Example;
create multiset volatile table Example
(
Alignment_No integer,
Alignment_Date date
)primary index( Alignment_No)
on commit preserve rows;

insert into Example(30, cast('20161007' as date format 'YYYYMMDD'));
insert into Example(31, cast('20161008' as date format 'YYYYMMDD'));
insert into Example(32, cast('20161009' as date format 'YYYYMMDD'));
insert into Example(40, cast('20161008' as date format 'YYYYMMDD'));
insert into Example(41, cast('20161009' as date format 'YYYYMMDD'));
insert into Example(42, cast('20161010' as date format 'YYYYMMDD'));
insert into Example(56, cast('20161101' as date format 'YYYYMMDD'));
insert into Example(57, cast('20161102' as date format 'YYYYMMDD'));
insert into Example(58, cast('20161103' as date format 'YYYYMMDD'));

select
Alignment_No,
min( Alignment_No ) over( order by Alignment_No desc
rows between 1 following and 1 following) as Alignment_No_Next,
Alignment_Date
from Example
qualify case when Alignment_No-1 = Alignment_No_Next then 0 else 1 end = 1

;