Solved

Difference between data in two lines

Posted on 2008-06-10
29
196 Views
Last Modified: 2010-04-21
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 ?

0
Comment
Question by:Mannsi
  • 12
  • 9
  • 8
29 Comments
 

Author Comment

by:Mannsi
ID: 21750583
Anybody ?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21756215
An example?
0
 

Author Comment

by:Mannsi
ID: 21757640
'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
 

Author Comment

by:Mannsi
ID: 21757715
But if by 'An example' you mean that there are very many ways of doing this, than yes, I require an example.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21759769
can you provide an example meaning some sample data and expected output from that data
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21759775
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
 

Author Comment

by:Mannsi
ID: 21759993
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21760076
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21760136
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
 

Author Comment

by:Mannsi
ID: 21760148
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
 
LVL 44

Expert Comment

by:GRayL
ID: 21760678
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
 
LVL 44

Expert Comment

by:GRayL
ID: 21760698
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21760724
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21760745
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Mannsi
ID: 21760830
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21760870
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
 
LVL 44

Expert Comment

by:GRayL
ID: 21760904
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
 

Author Comment

by:Mannsi
ID: 21760936
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 200 total points
ID: 21760946
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21761093
Oracle's SQL does not have FORMAT  You need to_char.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21761129
Thanks, I just realized I didn't need a format or equivalent, ergo my last post.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21761159
sdstuber:  BTW, how do we know this is Oracle SQL?  
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21761245
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21761351
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
 

Author Comment

by:Mannsi
ID: 21767028
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
ID: 21768768
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
 

Author Closing Comment

by:Mannsi
ID: 31465666
Thanks a bunch
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21770245
Thanks, glad to help.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21770572
you're welcome
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

18 Experts available now in Live!

Get 1:1 Help Now