?
Solved

Difference between data in two lines II

Posted on 2008-06-12
4
Medium Priority
?
255 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses

601 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