?
Solved

MySql getting endtime from an alternate row

Posted on 2014-09-23
15
Medium Priority
?
258 Views
Last Modified: 2014-09-24
I am trying to get starttime endtime by a number from a table and if it is the last number it should rap around to the first or be an arbitrary number in the future I thought this was right but it it not
SELECT min(UploadTime) as 'StartDate',Max(UploadTime)as 'EndDate', ToolNumber
 FROM ToolAssemblies
 WHERE ControllerSN = 1007 
GROUP BY ToolNumber ASC

Open in new window

My results are:endresult
What I need is: new
0
Comment
Question by:r3nder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
15 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40339548
If you want an end date of 1/1/2100 then you need to have that in a record.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40339564
Ok I will deal with that but how do I fix 14
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40339803
>>but how do I fix 14

What is wrong with it?  Your query makes sense - if the results are wrong (not what you expected) then check the source data, maybe that is wrong.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40339810
ps.  You have given relatively little information.  Have a good think about what you have as data and what you want from that data then reread your question.  Then ask yourself is it asking what I really want and letting others know enough to be able to realistically help.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40339862
I have given what the data is currently and what I would like it to be (in picture form)
I want the endtime of each row to be the start time of the next row. how can I accomplish this
0
 
LVL 6

Author Comment

by:r3nder
ID: 40339874
start   |      end| job
_______________
I/1/12 | 1/4/12 | 4
_______________
1/4/12 | 1/6/12| 12
_______________
1/6/12 | 1/18/12| 5

I want to "get " the starttime of the next row and set it to the end time of the current row
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40339907
>>I have given what the data is currently
Where, I don't see any start data, just end results.  Two different pictures, one of actual results, one of desired results.

>>and what I would like it to be (in picture form)
OK, you have that

>>I want the endtime of each row to be the start time of the next row.
That sounds rather different than what you said originally, but now clarifies what you wanted.

Can you process the results of the SQL in code (just get the value from the next data row, pretty easy) or must the SQL results have the start time of this record AND the end time (=start time of the next record) in one record ?
0
 
LVL 6

Author Comment

by:r3nder
ID: 40339992
The SQL results must have the start time of this record AND the end time (=start time of the next record) in one record or row
0
 
LVL 6

Author Comment

by:r3nder
ID: 40340159
if it helps this is what I have done so far - I can do an alter or update on the table I created - I just don't know how
DROP Table IF Exists precont;
Create Table precont as
(
SELECT min(UploadTime) as 'StartDate',Max(UploadTime) END as 'EndDate', ToolNumber,JobType 

 FROM ToolAssemblies
 WHERE ControllerSN = 1007 
GROUP BY ToolNumber ASC
);
SELECT p.StartDate as 'ControllerTime',j.jobid as 'Location','Job Downhole Equipment' As 'Event',Concat('ToolNumber: ',p.toolNumber,';','Tool Type: ',p.JobType,';','Run Number: ',jde.RunID,';', 'Tool Size: ', jde.ToolSize,';','Company: ',j.Company,';','Rig: ',j.Rig,';','Rig Number: ', j.RigNumber,';','Well Name: ',j.WellName) AS 'Event Notes'  
FROM JobDownholeEquipment AS jde
LEFT JOIN precont as p ON jde.ToolNumber = p.toolnumber
JOIN Job AS j on j.JobID = jde.JobID
WHERE jde.starttime >= p.startdate and jde.endtime<= p.enddate

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40340712
please provide the data produced by this query as text or in Excel

SELECT ControllerSN , ToolNumber, UploadTime, UploadTime
 FROM ToolAssemblies
 WHERE ControllerSN = 1007

This will give us raw data to work with.

Oh! and please tell us what version of SQL Server you are using (this can make a difference on what we choose as a solution)
0
 
LVL 6

Author Comment

by:r3nder
ID: 40341539
Here is the results in CSV for

 SELECT ControllerSN , ToolNumber, UploadTime AS 'StartTime', UploadTime AS 'EndTime'
  FROM ToolAssemblies
  WHERE ControllerSN = 1007
query.csv
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40341872
1007	11	2014-01-09 2:00:01	2014-01-09 2:00:01
1007	11	2014-01-09 2:00:01	2014-01-09 2:00:01
1007	11	2014-01-09 2:00:51	2014-01-09 2:00:51
1007	11	2014-01-09 2:00:56	2014-01-09 2:00:56
1007	11	2014-01-09 2:00:56	2014-01-09 2:00:56
1007	11	2014-02-18 23:02:15	2014-02-18 23:02:15
1007	14	2014-02-18 23:04:17	2014-02-18 23:04:17
1007	14	2014-02-18 23:04:32	2014-02-18 23:04:32
1007	5	2014-07-24 20:03:22	2014-07-24 20:03:22
1007	5	2014-07-24 20:03:34	2014-07-24 20:03:34
1007	5	2014-08-11 16:01:25	2014-08-11 16:01:25
1007	5	2014-08-11 16:01:36	2014-08-11 16:01:36

Open in new window

0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 2000 total points
ID: 40342112
This will work apart from not having a very final end time for the last record.  (There may be a quicker way to do this, some SQL servers have built in functionality to generate row numbers for example):

Query1
SELECT ToolAssemblies.ControllerSN, ToolAssemblies.ToolNumber, Min(ToolAssemblies.UploadTime) AS StartTime
FROM ToolAssemblies
GROUP BY ToolAssemblies.ControllerSN, ToolAssemblies.ToolNumber
HAVING (((ToolAssemblies.ControllerSN)=1007))
ORDER BY Min(ToolAssemblies.UploadTime);

Query2
SELECT (SELECT Count(Q1.StartTime) FROM Query1 as Q1 WHERE (Q1.StartTime <= Query1.StartTime) ) AS RowNumber, Query1.ControllerSN, Query1.ToolNumber, Query1.StartTime
FROM Query1;

Query3
SELECT Query2.ControllerSN, Query2.ToolNumber, Query2.StartTime, (SELECT StartTime FROM Query2 as Q2 WHERE Q2.RowNumber = Query2.RowNumber+1) AS EndTime
FROM Query2;



source
results
0
 
LVL 6

Author Comment

by:r3nder
ID: 40342272
Thanks I did this and at the end I just added
update table set EndTime = now() where entime IS NULL
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40342274
Thanks Andy
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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