Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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 : http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23471738.html#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.
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 : http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23471738.html#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.
Asked:
-
3
1 Solution
Hmmm, after thinking about the problem I think I can figure it out by my self. I will post again if I run into any big problems, but consider this question moot until then.
.....
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.
try this...
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 (partition by type ORDER BY time_val) next_time
FROM your_table)
WHERE COST = 0;
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 (partition by type ORDER BY time_val) next_time
FROM your_table)
WHERE COST = 0;
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now