All Forums Database
johnsunnydew 43 posts Joined 09/14
10 Jul 2016
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
 

AtardecerR0j0 71 posts Joined 09/12
12 Jul 2016
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

;

 

Be More!!

You must sign in to leave a comment.