Difference between data in two lines

Anybody ?

An example?

'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.

But if by 'An example' you mean that there are very many ways of doing this, than yes, I require an example.

can you provide an example meaning some sample data and expected output from that data

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.

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

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?

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.

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

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;

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;

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.

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.

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

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?

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?

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.

Oracle's SQL does not have FORMAT  You need to_char.

Thanks, I just realized I didn't need a format or equivalent, ergo my last post.

sdstuber:  BTW, how do we know this is Oracle SQL?  

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)

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)

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'.

Thanks a bunch

Thanks, glad to help.

you're welcome