Mannsi

asked on

# Difference between data in two lines II

Hello guys,

I had a question yesterday that I thought I had a perfect answere for. Well I got the perfect answere considering the information I gave the experts but I found one more problem with my code.

Here is the question from yesterday : https://www.experts-exchange.com/questions/23471738/Difference-between-data-in-two-lines.html?anchorAnswerId=21768768#a21768768

The code i'm using is the last one Sdstuber posted:

SELECT date_val,

CASE

WHEN next_time

- time_val >= 1

THEN TO_CHAR (FLOOR ( next_time

- time_val))

|| ' '

ELSE NULL

END

|| TO_CHAR ( TRUNC (SYSDATE)

+ ( next_time

- time_val), 'hh24:mi') time_diff

FROM (SELECT COST, date_val, time_val,

LEAD (time_val, 1) OVER (ORDER BY time_val) next_time

FROM your_table)

WHERE COST = 0;

Now, my problem is that there is one additional column called TYPE that takes values 1 ,2 or 3.

So my data table is like this

Cost----------Date_val ----------------Time_val------------TYPE

The code above compares two values of Time_val if the first line has a Cost = 0, but I need it to compare values of Time_val between a line with Cost = 0 and the next line with the same TYPE number.

Thanks in advance.

I had a question yesterday that I thought I had a perfect answere for. Well I got the perfect answere considering the information I gave the experts but I found one more problem with my code.

Here is the question from yesterday : https://www.experts-exchange.com/questions/23471738/Difference-between-data-in-two-lines.html?anchorAnswerId=21768768#a21768768

The code i'm using is the last one Sdstuber posted:

SELECT date_val,

CASE

WHEN next_time

- time_val >= 1

THEN TO_CHAR (FLOOR ( next_time

- time_val))

|| ' '

ELSE NULL

END

|| TO_CHAR ( TRUNC (SYSDATE)

+ ( next_time

- time_val), 'hh24:mi') time_diff

FROM (SELECT COST, date_val, time_val,

LEAD (time_val, 1) OVER (ORDER BY time_val) next_time

FROM your_table)

WHERE COST = 0;

Now, my problem is that there is one additional column called TYPE that takes values 1 ,2 or 3.

So my data table is like this

Cost----------Date_val ----------------Time_val--

The code above compares two values of Time_val if the first line has a Cost = 0, but I need it to compare values of Time_val between a line with Cost = 0 and the next line with the same TYPE number.

Thanks in advance.

ASKER

.....

Nobb, can't figure this one out. I thought that I could take GRayL's code from the previous problem and edit it but sadly I can't figure it out. Any help would be appreciated.

Nobb, can't figure this one out. I thought that I could take GRayL's code from the previous problem and edit it but sadly I can't figure it out. Any help would be appreciated.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

You da man !

ASKER