Solved

Difference between data in two lines II

Posted on 2008-06-12
4
221 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to Decrypt in C# a string that is encrypted with Coldfusion Encrypt(string, seed) function 11 103
Help Required 3 97
Query Syntax 17 36
VB.net and sql server 4 36
'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 …
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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