Solved

MySql getting endtime from an alternate row

Posted on 2014-09-23
15
253 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 37
VS 2017 18 52
Unwanted output from my query 5 15
show child records separated by commas 12 9
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

831 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