Solved

Difference between data in two lines III

Posted on 2008-06-16
8
169 Views
Last Modified: 2010-04-21
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
Comment
Question by:Mannsi
  • 4
  • 4
8 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 21804269
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
 

Author Comment

by:Mannsi
ID: 21810833
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
 
LVL 29

Expert Comment

by:Badotz
ID: 21811580
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
 

Author Comment

by:Mannsi
ID: 21813107
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Accepted Solution

by:
Badotz earned 500 total points
ID: 21813438
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
 

Author Comment

by:Mannsi
ID: 21813561
I am going home from work now but I will try your solution tomorrow and post my progress/questions.

Thanks
0
 

Author Closing Comment

by:Mannsi
ID: 31467555
Thanks, I took your code as base and built on it.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21845042
No worries - glad to help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query question 4 30
SQL query 4 31
encyps queries mssql 15 27
Sql to Replace Folderpath string in MS Access Table field 7 20
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

13 Experts available now in Live!

Get 1:1 Help Now