SQL - How to join query results in same line
I have an application that save the working time based on machine, operation, operator and reference.
My query returns the data like this:
u_reference | u_user | u_operation | u_machine | price | u_action | Start | Work time P-2000-9429 | John | F3X | 144 | 60 | PLAY |2019-11-08 09:40:53| 00:00:00 P-2000-9429 | John | F3X | 144 | 60 | PAUSE |2019-11-08 18:00:45| 08:19:52 P-2000-9429 | John | F3X | 144 | 60 | REPLAY |2019-11-09 07:13:23| 08:19:52 P-2000-9429 | John | F3X | 144 | 60 | PAUSE |2019-11-09 12:19:21| 13:25:49 P-2000-9429 | John | F3X | 144 | 60 | REPLAY |2019-11-11 07:09:50| 13:25:49 P-2000-9429 | John | F3X | 144 | 60 | STOP |2019-11-11 11:03:26| 17:19:21
select u_daily_work_alb.u_reference, u_daily_work_alb.u_user, u_daily_work_alb.u_operation, u_machines_alb.u_design as u_machine, u_machines_alb.epcusto as price, u_daily_work_alb.u_action, u_daily_work_alb.u_created_at as Start, u_daily_work_alb.u_work_time as Work_time from u_daily_work_alb inner join u_machines_alb on u_machines_alb.id = u_daily_work_alb.u_machineId where u_daily_work_alb.bostamp = 'ALB472248D84210CFA9FE30' and u_daily_work_alb.u_reference = 'p-2000-9429' order by u_daily_work_alb.id asc
bostamp column is like an id.
Now what I need is a result like this:
u_reference | u_user | u_operation | u_machine | price | Start |END | Work time P-2000-9429 | John | F3X | 144 | 60 |2019-11-08 09:40:53|2019-11-08 18:00:45| calculate time P-2000-9429 | John | F3X | 144 | 60 |2019-11-09 07:13:23|2019-11-09 12:19:21| calculate time P-2000-9429 | John | F3X | 144 | 60 |2019-11-11 07:09:50|2019-11-11 11:03:26| calculate time
I think the column
u_action is important because we know that after a play we have a pause or stop. After a pause we have a replay or stop and after a replay we have a pause or stop.
How can I do that?
I have (possibly incorrectly) assumed T-sql. However I think a similar approach could be used in most databases.
I have set up an example below using the tables you provided - you would need to change this to match your underlying query.
basically I get all the start events and assign them a row number based on their event time - i then left join them to all the end events based on that row number.
if it is possible to skip an end event (a start with no pause or stop) - this will cause you problems.
CREATE TABLE dbo.SampleData ( u_reference varchar(100), u_user varchar(100), u_operation varchar(100), u_machine varchar(100), price varchar(100), u_action varchar(100), [Start] datetime, [Work time] datetime ) INSERT INTO dbo.SampleData (u_reference, u_user, u_operation, u_machine, price, u_action, [Start], [Work time]) VALUES ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'PLAY' ,'2019-11-08 09:40:53', '00:00:00'), ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'PAUSE' ,'2019-11-08 18:00:45', '08:19:52'), ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'REPLAY' ,'2019-11-09 07:13:23', '08:19:52'), ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'PAUSE' ,'2019-11-09 12:19:21', '13:25:49'), ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'REPLAY' ,'2019-11-11 07:09:50', '13:25:49'), ('P-2000-9429' , 'John' , 'F3X' , '144' , '60' , 'STOP' ,'2019-11-11 11:03:26', '17:19:21') SELECT A.u_reference, A.u_user, A.u_operation, A.u_machine, A.price, A.[Start], B.[Start] AS EndTime, CONVERT(varchar, DATEADD(second, (DATEDIFF(SECOND,A.[Start],B.[Start])), 0), 108) AS WorkingTime FROM ( -- All Start/Restart Events SELECT *, ROW_NUMBER() OVER (ORDER BY [Start]) as Rownum FROM dbo.SampleData WHERE u_action IN ('PLAY','REPLAY') ) A LEFT JOIN ( -- All End Events SELECT *, ROW_NUMBER() OVER (ORDER BY [Start]) as Rownum FROM dbo.SampleData WHERE u_action IN ('PAUSE','STOP') ) B ON A.Rownum = B.Rownum ORDER BY A.[Start]
The Output is;
SQL multiple joins for beginners with examples, This article helps us to understand SQL multiple joins concept with examples. multiple join Duration: 3:10 Posted: Oct 16, 2019 JOIN – You can use joins to combine columns from one or more queries into one result. UNION – Use Unions and other set operators to combine rows from one or more queries into one result. Sub Queries – Sometimes called nested queries, these can be used to perform a separate search in the database showed results can be used in another query.
You could achieve this by using lead function if you are using Sql-server.
select u_reference, u_user,u_operation,u_machine,price,[Start],[End Time],WorkingTime from ( SELECT A.u_reference,A.u_user,A.u_operation,A.u_machine,A.price,A.[Start],A.u_action,lead(Start) over(order by Start) as [End Time], CONVERT(varchar, DATEADD(second, (DATEDIFF(SECOND,[Start],lead(Start) over(order by Start))), 0), 108) AS WorkingTime from #SampleData A)B where u_action like '%play%'
Combine results from several SQL tables, What are the ways I can combine results from more than one query? SQL If you join two tables, one containing 5 row, and the other 10, the result may contain� First, execute each SELECT statement individually. Second, combine result sets and remove duplicate rows to create the combined result set. Third, sort the combined result set by the column specified in the ORDER BY clause. In practice, we often use the UNION operator to combine data from different tables.
If I understand correctly:
select u_reference, u_user, u_operation, u_machine, start, next_created_at as end, <time calculation> -- this is database dependent from (select dw.u_reference, dw.u_user, dw.u_operation, m.u_design as u_machine, m.epcusto as price, dw.u_action, dw.u_created_at as Start, dw.u_work_time as Work_time, lead(dw.u_created_at) over (partition by dw.u_reference order by dw.u_created_at) as next_created_at, lead(dw.u_work_time) over (partition by dw.u_reference order by dw.u_created_at) as next_work_time from u_daily_work_alb dw join u_machines_alb m on m.id = dw.u_machineId where dw.bostamp = 'ALB472248D84210CFA9FE30' and dw.u_reference = 'p-2000-9429' ) dw where u_action in ('PLAY', 'REPLAY') order by dw.id asc;
This assumes that the record after a "play"/"replay" provides the information for ending.
Also, it is unclear what keys to use for defining "next". I don't know if user, operation, and machine (or some combination of them) are needed apart from the reference and time.
Learn How to Combine Data with a CROSS JOIN, All row combinations are included in the result; this is commonly called cross product This query returns the same results as one written with an INNER JOIN . Paste the SQL statement for the select query into the SQL view object tab of the union query. Delete the semicolon (;) at the end of the select query SQL statement. Press Enter to move the cursor down one line, and then type UNION on the new line. Click the tab for the next select query that you want to combine in the union query.
Documentation: 8.2: Joins Between Tables, The SQL Language, Fast Forward � Next Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the table are being processed at the same There is no result row for the city of Hayward. Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
SQLite Self-join - Joining a Table to Itself, Because you cannot refer to the same table more than one in a query, you to join the employees table to itself, the result set does not have the row whose� Question by Orbison · Oct 29, 2009 at 01:37 PM · t-sql query Displaying fields from a result set on the same line Hi Guys, I have the following SQL Statement which produces 4 lines of output for two stores A and B. Record 1 output belongs to Store A and records 2,3 and 4 belong to Store B.
There are not that many original problems in Transact-SQL. Most of the problems have well known solutions. The only tricky part in finding a solution to a common problem is in figuring out the type of the problem. In this article I want to show one such common problem - displaying grouped query results in one line. Problem Definition
- which dbms product are you using?
- At the question there is no DBMS tag at the moment. Does this query work on any of the DBMS's?
- I did this in t-sql. I think this would also work in oracle.. Not sure about other providers.
- @JamesCooke I detect a problem in this result if I have new lines with different u_operation and u_machine. the endTime is always de first stop and it could have several stops based on u_operation, u_machine and u_reference
- I think i understand the problem - you need to partition by the different machines or operations you want to look at (Its hard for me to know exactly without knowing your data). But I would advice you modify the query provided to ROW_NUMBER() OVER (PARTITION BY What you want to Partition By ORDER BY [Start]) as Rownum This kind of thing is what I meant with "you will need to change this to match your underlying query"
- So based on what you are saying I would replace ROW_NUMBER() OVER (ORDER BY [Start]) With ROW_NUMBER() OVER (PARTITION BY u_reference, u_operation, u_machine ORDER BY [Start])
- Hi @GordonLinoff I try use your query but end time is always the next value. For example I can have two plays from same reference with differents operations so it has to detect what is the next pause or stop for that reference, machine and operation
- @user3242861 . . . That is intentional. That suggests that I did not understand the question correctly.