Require sql query for this issue.

Database
gkk
Enthusiast

Require sql query for this issue.

CREATE MULTISET VOLATILE TABLE REQ_QUERY

(

 

POL_ID VARCHAR(20)

, START_DT DATE FORMAT 'YYYY-MM-DD'

, END_DT DATE FORMAT 'YYYY-MM-DD'

) ON COMMIT PRESERVE ROWS;

 

insert into REQ_QUERY ( '112813',                     '2008-07-01',           '2009-02-28');

insert into REQ_QUERY ( '112813',                     '2009-03-01',            '2009-05-30');

insert into REQ_QUERY ( '112813',                     '2009-07-01',            '2010-02-11');

insert into REQ_QUERY ( '112813',                     '2010-02-12',            '2010-06-30');

insert into REQ_QUERY ( '112813',                     '2010-07-01',            '2011-04-03');

insert into REQ_QUERY ( '112813',                     '2011-04-04',            '2011-04-24');

insert into REQ_QUERY ( '112813',                     '2011-04-25',            '2011-05-23');

insert into REQ_QUERY ( '112813',                     '2011-05-24',            '2011-06-17');

insert into REQ_QUERY ( '112813',                     '2011-06-18',            '2011-06-19');

insert into REQ_QUERY ( '112813',                     '2011-06-20',            '2011-06-30');

insert into REQ_QUERY ( '112813',                     '2011-07-01',            '2011-08-31');

insert into REQ_QUERY ( '112813',                     '2011-09-01',            '2012-06-30');

 

 

SEL B.*,DAYS_DIFF  FROM

(

SEL  A.*,

ROW_NUMBER() OVER (ORDER BY START_DT)  AS REC_SQ FROM REQ_QUERY A

) B

 

 

 

DAYS_DIFF DERIVED COLUMN :

DAYS DEFERENCE BETWEEN REC_SQ = 1 END_DATE    AND REC_SQ = 2  START_DATE

DAYS DEFERENCE BETWEEN REC_SQ = 2 END_DATE    AND REC_SQ = 3  START_DATE

DAYS DEFERENCE BETWEEN REC_SQ = 3 END_DATE    AND REC_SQ = 4  START_DATE 

.

.

.

.

 

EXPECTED OUT PUT :

 

POL_ID   START_DT             END_DT REC_SQ                  DAYS_DIFF

112813    2008-07-01              2009-02-28              1                                              1

112813    2009-03-01              2009-05-30              2                                              32

112813    2009-07-01              2010-02-11              3                                              1

112813    2010-02-12              2010-06-30              4                                              1

112813    2010-07-01              2011-04-03              5                                              1

112813    2011-04-04              2011-04-24              6                                              1

112813    2011-04-25              2011-05-23              7                                              1

112813    2011-05-24              2011-06-17              8                                              1

112813    2011-06-18              2011-06-19              9                                              1

112813    2011-06-20              2011-06-30              10                                            1

112813    2011-07-01              2011-08-31              11                                            1

112813    2011-09-01              2012-06-30              12                                            NULL

 

 

Please help me in writing this query ...

 

Regards,

gkk

Tags (1)
4 REPLIES
Senior Supporter

Re: Require sql query for this issue.

this should do 


select POL_ID,

       START_DT,

       end_dt,

       row_number() over (partition by POL_ID order by start_dt),

       miN(start_dt) over (partition by POL_ID order by start_dt rows between \

1 following and 1 following) - end_dt

from REQ_QUERY

Re: Require sql query for this issue.

select cast(end_date as date) - cast(start_date as date) will return the number of days. 

gkk
Enthusiast

Re: Require sql query for this issue.

Hi ulrich,

can you please elaborate the query .. i am confused with this

miN(start_dt) over (partition by POL_ID order by start_dt rows between \

1 following and 1 following) - end_dt

part in your query .

and what ever mr. jhinman732 sir replied is not what i am expecting .. i am not calculating the days b/w

the two values of the same record. in the expected out the days diff value 32 is because of

112813    2009-03-01                  2009-05-30              2                                              32

112813    2009-07-01              2010-02-11                  3                                              1

 

  2009-05-30   minus   2009-07-01   = 32 days

Regards,

gkk

Senior Supporter

Re: Require sql query for this issue.

with OLAP functions (check the manual) you have access to values of different rows

miN(start_dt) over (partition by POL_ID order by start_dt rows between 1 following and 1 following)

Will give you the start_Dt of the next row in respect to the ordering by start_dt