Difference between data in two lines

Hello experts,
I have a table in a database that is built up like this :

Cost       Date_val         Time_val

I need to make a SQL query that returns Time_val value difference between two lines and the Date_val value of the first line. I need to do this only when the Cost value in the first line is 0 (it will never be 0 two times in a row).
How should my query look like ?

MannsiAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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;
0
 
MannsiAuthor Commented:
Anybody ?
0
 
GRayLCommented:
An example?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
MannsiAuthor Commented:
'An example ?'
I don't understand ... are you asking if I need an example ?
I am unsure how to compare the value difference of two numbers between lines in a query. If this is very difficult or impossible please let me know so I can stop trying to figure it out. I need to know how my query should look like that gives me this result.
0
 
MannsiAuthor Commented:
But if by 'An example' you mean that there are very many ways of doing this, than yes, I require an example.
0
 
sdstuberCommented:
can you provide an example meaning some sample data and expected output from that data
0
 
sdstuberCommented:
also, it helps if you post to the database zones as well as just the sql syntax.  So we know which database you're looking for.
The way to write the query can vary drastically based on the platform.
0
 
MannsiAuthor Commented:
Ok, sorry for the confusion on my part.

I don't have any sql syntax right now. I'm using an oracle database using VBA through Excel.

The data looks like this in my database:

Cost      Date_val      Time_val
32      2.1.2008 00:00      02.01.2008 01:13:00
30      2.1.2008 00:00      02.01.2008 03:50:00
32      2.1.2008 00:00      02.01.2008 04:53:00
0      2.1.2008 00:00      02.01.2008 05:29:00
32      2.1.2008 00:00      02.01.2008 06:00:00
30      2.1.2008 00:00      02.01.2008 06:26:00
32      2.1.2008 00:00      02.01.2008 06:55:00
30      2.1.2008 00:00      02.01.2008 07:08:00
32      2.1.2008 00:00      02.01.2008 07:51:00
28      2.1.2008 00:00      02.01.2008 10:45:00

But I need the format to be:
Date_val      Time_diff
2.1.2008 00:00      00:31

Where time_diff is the difference between Time_val in line 4 and 5 since line 4 contains a Cost of 0.

Is there any way of doing an Select (Time_val-NextLine(Time_Val)) or are there any simple ways of doing what I need to happen ?
I hope this helps
0
 
sdstuberCommented:
are date_val and time_val  strings that look like that?  or are they dates/timestamp types that you're formatting to look like that?
0
 
sdstuberCommented:
Assuming they are dates try this...
SELECT date_val,
       TO_CHAR (  TRUNC (SYSDATE)
                + (  next_time
                   - time_val),
                'hh24:mi:ss'
               ) 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


note, I assumed the difference in time_val will never be greater than 24 hours.
0
 
MannsiAuthor Commented:
In my database Date_val is a DATE type that I just read out but the Time_val I formated using the to_char() function in the SQL query that got thist data. Time_val is also of type DATE
0
 
GRayLCommented:
Assuming you table is named myTable:

SELECT a.Date_Val, (SELECT Min(b.Time_val) FROM myTable b WHERE b.Time_val>a.Time_Val) AS Time_Diff FROM myTable a WHERE a.Cost = 0;

0
 
GRayLCommented:
Sorry forgot a part:

SELECT a.Date_Val, (SELECT Min(b.Time_val) FROM myTable b WHERE b.Time_val>a.Time_Val) - a.Time_val AS Time_Diff FROM myTable a WHERE a.Cost = 0;

0
 
sdstuberCommented:
GRayL,  how does that query return a time difference?

That will simply return two dates,  the date_val and the   02.01.2008 06:00:00 time.


and it requires two queries to the same table, even with an index, that's unnecessary io.
0
 
sdstuberCommented:
ah, ok,  too slow on my response.

that will return a time difference,  but
it will return a number (0.0215277777777778)
representing the fraction of a day,  not a formatted to show 31 minutes.
0
 
MannsiAuthor Commented:
How do I change the format ??? If I try to do a to_char() around 'b.Time_val>a.Time_Val) - a.Time_val' I get an error as the first right parenthesis messis things up
0
 
sdstuberCommented:
you can't directly change a number into a formatted character.

You have to turn the number into a date then format just the time portion.
Or you have to break the day fraction down in to days/hours/minutes/seconds yourself.

Does the query I provided not work for you?  If not,  what else does it need?
0
 
GRayLCommented:
If you want it in minutes:

SELECT a.Date_Val,
Format(((SELECT Min(b.Time_val) FROM myTable b WHERE b.Time_val>a.Time_Val) - a.Time_val) * 1440,"nn") AS Time_Diff FROM myTable a WHERE a.Cost = 0;


Does SQL have the Format function?
0
 
MannsiAuthor Commented:
I'm sorry but I have to go home from work now, but thank you guys, you really helped me.
sdstuber : Sorry but among all the posts I just didn't see your code, I will try it tomorrow.
0
 
GRayLConnect With a Mentor Commented:
Sorry, that was wrong.  With 1440 minutes in a day:

SELECT a.Date_Val,
((SELECT Min(b.Time_val) FROM myTable b WHERE b.Time_val>a.Time_Val) - a.Time_val) * 1440 AS Time_Diff FROM myTable a WHERE a.Cost = 0;
0
 
sdstuberCommented:
Oracle's SQL does not have FORMAT  You need to_char.

0
 
GRayLCommented:
Thanks, I just realized I didn't need a format or equivalent, ergo my last post.
0
 
GRayLCommented:
sdstuber:  BTW, how do we know this is Oracle SQL?  
0
 
sdstuberCommented:
we know it's Oracle, because he said so.  :)

ID:21759993

Incidentally,  to get the time formatted correctly using GRayL's query, it might look something like the below...
Note, again this assumes there is less than 24 hours between times.
(And again, I would avoid this method, as it it's inefficient compared to using the LEAD function)

If there is more than 24 hours,  how do you want it formatted?

SELECT date_val, TO_CHAR (  TRUNC (SYSDATE)
                          + time_diff, 'hh24:mi:ss') time_diff
  FROM (SELECT a.date_val,
                 (SELECT MIN (b.time_val)
                    FROM mytable b
                   WHERE b.time_val > a.time_val)
               - a.time_val AS time_diff
          FROM mytable a
         WHERE a.COST = 0)
0
 
sdstuberCommented:
ooops  I see I was adding functionality.  You didn't want seconds in your output, only hours/minutes....
again, how do you want more than 24 hours to be displayed?

So,   my version....

SELECT date_val,
       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;

GRayL's version....


SELECT date_val, TO_CHAR (  TRUNC (SYSDATE)
                          + time_diff, 'hh24:mi') time_diff
  FROM (SELECT a.date_val,
                 (SELECT MIN (b.time_val)
                    FROM mytable b
                   WHERE b.time_val > a.time_val)
               - a.time_val AS time_diff
          FROM mytable a
         WHERE a.COST = 0)


0
 
MannsiAuthor Commented:
Is there some way to only show days when there is in fact a whole day ?  I think it would look better that way but if it's too complicated I would settle for days in all the output. I tried changing the format in sdstuber's version to 'D hh24:mi' but it always gives me '4 xx:xx'.
0
 
MannsiAuthor Commented:
Thanks a bunch
0
 
GRayLCommented:
Thanks, glad to help.
0
 
sdstuberCommented:
you're welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.