Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Difference between data in two lines III

Hello experts,
This is a third question regarding a problem of mine. The first can be found here :

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23471738.html

Along with many answered question regarding my system and the database I'm working with. There are probably answers there that can be edited to answer this question.

My problem now is that I need to make a query that returns a time difference between two lines. This is an example of my database table.

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

I need the difference in Time_val value of the second line with Cost = 47 and the next line after the first line with Cost = 47. I hope that makes sense.

My output from the data above should be
Time_val_begining            Time-difference
02.01.2008 04:53             02:00
02.01.2008 06:55             01:01

I can get the difference between the two Cost= 47 lines using for example a queries like the ones sdstuber and GRayL posted as answers to the first question but I can't seem to figure out how to do this.
Thanks in advance.



 
0
Mannsi
Asked:
Mannsi
  • 4
  • 4
1 Solution
 
BadotzCommented:
Might it not be easier to get the time difference between *all* the cost:47 rows, and to then discard the first in your application?
0
 
MannsiAuthor Commented:
I'm not sure I follow. The way my data stands in the first post, I need the difference between lines 3 and 6 and lines 7 and 9. Are you saying I can get the difference between two 47 lines but somehow skip to the second first line afterwards ?
0
 
BadotzCommented:
Ah, it's even worse than I thought...

It might be easier to do this in your application than in your query. See pseudo-code following the data table.
/*
 
Line    Cost    Date_val            Time_val
 1      32      2.1.2008 00:00      02.01.2008 01:13
 2      47      2.1.2008 00:00      02.01.2008 03:50
 3      32      2.1.2008 00:00      02.01.2008 04:26
 4       0      2.1.2008 00:00      02.01.2008 05:29
 5      32      2.1.2008 00:00      02.01.2008 06:00
 6      47      2.1.2008 00:00      02.01.2008 06:26
 7      32      2.1.2008 00:00      02.01.2008 06:50
 8      30      2.1.2008 00:00      02.01.2008 07:08
 9      47      2.1.2008 00:00      02.01.2008 07:51
10      28      2.1.2008 00:00      02.01.2008 10:45
 
*/
 
/* pseudo-code:
 
start_date = ''
date_was = ''
start_time = 0
end_time = 0
save_next_row = 0
 
do
   
   if start_time != 0 then
      if cost = 47 then
         end_time = time_val
         output date_was + (end_time - start_time)
         start_date = ''
         date_was = ''
         start_time = 0
         end_time = 0
      end if
   end if
   
   if cost = 47 then 
      save_next_row = 1
   end if
   
   if save_next_row = 0 then
      if start_time = 0 then
         date_was = date_val
         start_time = time_val
      end if
   end if
   
   --save_next_row
   
   move to next row
 
loop while more rows remain
 
*/

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MannsiAuthor Commented:
If anybody knows of a more simpler solution feel free to post :)

I haven't gone through your code in detail since it will probably take me some time, but could I use a VBA recordset to do this ? If I could, are there any obvious tricks I should know about ?

Thanks for thinking about my problem.
0
 
BadotzCommented:
I added comments to the "code" below.

Basically it says:

1. If we have a starting time, then if this cost=47, then get the end time and do something with the elapsed time

2. If this cost=47, then set a flag so we can grab the next row

3. If this is the next row, set the starting date and time values

4. Decrement the next row counter

5. If there are more rows, then go back to step 1
/* pseudo-code:
 
// Initialize variables
 
var start_date = ''
var date_was = ''
var start_time = 0
var end_time = 0
var save_next_row = 0
 
do
   
   if start_time != 0 then	// got a start time now?
      if cost = 47 then		// is this the right COST?
         end_time = time_val	// yes, so save the end time
         output date_was + (end_time - start_time)	// do something with the elapsed time
         
         // reset the variables
         
         start_date = ''
         date_was = ''
         start_time = 0
         end_time = 0
      end if
   end if
   
   if cost = 47 then 		// is this the right COST?
      save_next_row = 1		// yes, so we want the values from the next row
   end if
   
   if save_next_row = 0 then	// is this the next row?
      if start_time = 0 then	// do we need a start time? (redundant test)
         date_was = date_val	// save the date value for later
         start_time = time_val	// save the starting time value for later
      end if
   end if
   
   save_next_row = save_next_row - 1	// decrement row counter
   
   move to next row		// move to next row in the recordset
 
loop 				// loop while more rows remain
 
*/

Open in new window

0
 
MannsiAuthor Commented:
I am going home from work now but I will try your solution tomorrow and post my progress/questions.

Thanks
0
 
MannsiAuthor Commented:
Thanks, I took your code as base and built on it.
0
 
BadotzCommented:
No worries - glad to help.
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now