Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Difference between data in two lines II

Posted on 2008-06-12
4
Medium Priority
?
251 Views
Last Modified: 2010-04-21
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------------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.
0
Comment
Question by:Mannsi
[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
 

Author Comment

by:Mannsi
ID: 21770447
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.
0
 

Author Comment

by:Mannsi
ID: 21776771
.....

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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 21777781
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;
0
 

Author Closing Comment

by:Mannsi
ID: 31466553
You da man !
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question