MySql getting endtime from an alternate row

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
LVL 6
r3nderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
If you want an end date of 1/1/2100 then you need to have that in a record.
0
r3nderAuthor Commented:
Ok I will deal with that but how do I fix 14
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

AndyAinscowFreelance programmer / ConsultantCommented:
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
r3nderAuthor Commented:
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
r3nderAuthor Commented:
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
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
r3nderAuthor Commented:
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
r3nderAuthor Commented:
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
PortletPaulfreelancerCommented:
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
r3nderAuthor Commented:
Here is the results in CSV for

 SELECT ControllerSN , ToolNumber, UploadTime AS 'StartTime', UploadTime AS 'EndTime'
  FROM ToolAssemblies
  WHERE ControllerSN = 1007
query.csv
0
PortletPaulfreelancerCommented:
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
AndyAinscowFreelance programmer / ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
Thanks I did this and at the end I just added
update table set EndTime = now() where entime IS NULL
0
r3nderAuthor Commented:
Thanks Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.