Solved

Difference between data in two lines II

Posted on 2008-06-12
4
214 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
  • 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 73

Accepted Solution

by:
sdstuber earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now