Total count of the days SQL....

[kumariaru]

Good Morning All,
I have a scenario where I have to get the total count of the days.
Data in the table:

col1 col2 col3
101 12/12/06 A
101 02/15/07 H
101 03/10/07 B
101 04/18/07 C
101 05/27/07 H
101 06/30/07 C
101 06/31/07 H
101 07/04/07 S
102 07/13/07 A
102 07/30/07 B

All the three columns are keys. I have to check for the 'H' in col3 and get the count of days based on immediate record (ex : days between of 02/15/07 & 03/10/07, 05/27/07 & 06/30/07 and 06/31/07 & 07/04/07). And get the total sum of the days.
I have to do it in SQL.

Thanks in advance

 

[IanWaterman]

You should be able to do this using the Lead function, this link will tell you how to use it It describes Lag but lead is just the opposite. However, you will need a field which defines the order of your data.

http://www.adp-gmbh.ch/ora/sql/analytical/lag.html

Having got your lead value into same row you can then calculate difference.

Ian

 

[kumariaru]

Thank you Ian,

 

[taupirho]

SQL> select * from tom;

DT I
--------- -
12-DEC-06 A
15-FEB-07 H
10-MAR-07 B
18-APR-07 C
27-MAY-07 H
30-JUN-07 C
01-JUL-07 C

7 rows selected.


1 select id,todate-fromdate days,id ,todate,fromdate
2 from
3 (
4 select dt fromdate,lead(dt) over(order by dt) todate,id
5 from tom
6 )
7 where todate is not null
8* and id = 'H'
SQL> /

I DAYS I TODATE FROMDATE
- ---------- - --------- ---------
H 23 H 10-MAR-07 15-FEB-07
H 34 H 30-JUN-07 27-MAY-07



In order to understand recursion, you must first understand recursion.