Solved

MySql getting endtime from an alternate row

Posted on 2014-09-23
15
254 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL recovery 7 30
SQL Server syntax 11 41
Updating a single record in forEach using C#/Entity 7 25
restriction of entering a a page 5 33
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. …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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