CODEWITHSUNDEEP
sqlsql-server
Arrow
Arrow

Reputation: 35

How to combine Two Select statement to One SQL Statement

I am trying to calculate Total Worked hour for the employees worked in night shift which Time In will be Yesterday evening Punched In and Time Out will be Next day Morning Punch

I tryied with two seperate simple queries and I need to get Time In & Time Out in Same Query. If any one can help with this I am glad. Below is the Two select statements which I tried need to combine

SELECT
EMP_Punch_Date'DATE',
MAX(EMP_Punch_Time)'TIME IN'
FROM EMP_Event_Log
WHERE EMP_Punch_Date='09-02-2018'
GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time

SELECT 
EMP_Punch_Date'DATE',
MIN(EMP_Punch_Time)'TIME OUT'
FROM EMP_Event_Log
WHERE EMP_Punch_Date='09-03-2018'
GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time

enter image description here

Upvotes: 0

Views: 430

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You can use join between 2query by using sub-query

select max(t1.TIMEIN),min(t2.TIMEOUT),t1.DATE,t1.EMP_Token_No from
    (
    SELECT
    EMP_Punch_Date as 'DATE',
    MAX(EMP_Punch_Time) as 'TIMEIN'
    FROM EMP_Event_Log
    WHERE EMP_Punch_Date='09-02-2018'
    GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time
    ) as t1
    join
    (
    SELECT 
    EMP_Punch_Date as 'DATE',
    MIN(EMP_Punch_Time) as 'TIMEOUT'
    FROM EMP_Event_Log
    WHERE EMP_Punch_Date='09-03-2018'
    GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time
    ) t2 on t1.EMP_Token_No=t2.EMP_Token_No 
    group by t1.DATE,t1.EMP_Token_No

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

If EMP_Punch_Date is date or datetime type.

You can try to use BETWEEN to combine your two query, because of there is only one difference between to query is get MAX or MIN on EMP_Punch_Time column.

and remove EMP_Punch_Time in group by

SELECT
    EMP_Punch_Date 'DATE',
    MAX(CASE WHEN EMP_Punch_Date = '09-02-2018' THEN EMP_Punch_Time END) 'TIME IN' , 
    MIN(CASE WHEN EMP_Punch_Date = '09-03-2018' THEN EMP_Punch_Time END) 'TIME OUT'
FROM EMP_Event_Log
WHERE EMP_Punch_Date BETWEEN '09-02-2018' AND '09-03-2018'
GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date

Upvotes: 2

Related Questions