Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
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.
COURSE of the MONTH
14 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Difference between data in two lines II
Posted on 2008-06-12
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
4 Comments
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.
Active today
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;
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 19 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.