Solved

MySql getting endtime from an alternate row

Posted on 2014-09-23
15
249 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
  • 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
 
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
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.

 
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 48

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 48

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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now