CODEWITHSUNDEEP
sqlsql-server
vinnu
vinnu

Reputation: 11

SQl query to subtract two rows for the same id and populate the difference in a new column

Here are my input table. I am trying to populate the table as shown in the output below. Could you please help me with the SQL query in SQL Server.

In the table1, ID is unique and each ID has a timein and timeout.

Table 2 represents more detailed action about the ID in table1 between timein and timeout. But this Tables just has timein. Using both table 1 and table 2 have to populate the output tables as shown with actual time in mins.

Output table row1 equals table1 row1 with time diff in minutes as actualtime

Output table row2 equals table2 row1 and table2 row2 timein as timeout and time diff in minutes as actualtime

Output table row3 equals table2 row2 and table2 row3 timein as timeout and time diff in minutes as actualtime

Output table row4 equals table2 row3 and table1 row1 timeout as timeout and time diff in minutes as actualtime.

Table 1

ID     statusName1     Timein     Timeout    
--------------------------------------------
100     WITH M         9:00:00     10:15:00
101     WITH K        10:00:00     13:30:00

Table 2

 key ID     statusName2        Timein     
 ----------------------------------
 1  100       WITH A          9:05:00     
 2  100       WITH B          9:20:00     
 3  100       WITH C          9:45:00
 4  101       WITH A         10:01:00
 5  101       WITH D         10:11:00

My output should look like

 ID      statusName       TimeIn        Timeout    Actualtime     
 -------------------------------------------------------------
 100      WITH M           9:00:00      10:15:00     75:00         
 100      WITH A           9:05:00      9:20:00      15:00      
 100      WITH B           9:20:00      9:45:00      25:00     
 100      WITH C           9:45:00      10:15:00     30:00
 101      WITH K          10:00:00      13:30:00    210:00
 101      WITH A          10:01:00      10:11:00     10:00
 101      WITH D          10:11:00      13:30:00    199:00

Upvotes: 0

Views: 294

Answers (1)

masonshu
masonshu

Reputation: 45

You need to add a column to table2, so as to do left join.

 ID     statusName        Actualtime    TimeIn        
 100       WITH A         9:05:00       9:00:00
 100       WITH B         9:20:00       9:00:00
 100       WITH C         9:45:00       9:00:00
 101       WITH M         10:01:00      10:00:00
 101       WITH N         10:11:00      10:00:00

select table1.ID, table2.statusName, 
      table1.TimeIn, table1.Timeout, table2.Actualtime
from table1 LEFT JOIN
     table2
     ON table1.TimeIn= table2.TimeIn;

Upvotes: 1

Related Questions