LEFT JOIN Multiple rows from the same table | SQL SERVER 2012

Question

I have a problem with this query. I don't know how to approach this since the data is within the same column and can only be separated with the labesl I, and O. how will I approach this? Is this possible via multiple LEFT JOIN?

SELECT
CHECKINOUT.userid as chkuserid, USERINFO.userid as usruserid, name, 
BADGENUMBER as badge,
LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,checktype,
Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
from CHECKINOUT,USERINFO
where CHECKINOUT.userid=USERINFO.userid
and BADGENUMBER = '1693'

This is what the query looks like.

What I want to achieve is for it to look like this.

May I ask for assistance regarding this matter?

If you will try to query in the same table you can use cte([Common Table Expression](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example)) — Buchiman, Aug 13 '19 at 03:10
Depending on how far into the project you are, it might be wise to change the structure of the database to have checkin and checkout as datetime fields as it will save some grief for the edge cases of split shifts and working past midnight. — Tim Morton, Aug 13 '19 at 03:23
@TimMorton, yeah, that is what I'm worried as well. I don't know how to approach it like that. Do you have a link that I can study? I really need to look on that. — pjustindaryll, Aug 13 '19 at 03:24
No links for you, just the encouragement to have a datetime field designated for in, and one for out. This allows you to use the database’s internal date time functions to calculate (on the fly) the time logged in as its own column. Don’t split day and time into two columns; it works much better to use datetime (that solves the midnight problem). For split shifts there would be two entries. Working with the database queries is dead simple that way; the hardest part is selecting the right record to update when checking out. — Tim Morton, Aug 13 '19 at 03:49
@TimMorton, thank you. I'll look into it. I think I need to restructure my database soon. — pjustindaryll, Aug 13 '19 at 06:57

score 1 · Accepted Answer · answered Aug 13 '19 at 03:18

how about this.

select t1.*, t2.checktype, t2.timestamp from 
    (SELECT
    CHECKINOUT.userid as chkuserid, USERINFO.userid as usruserid, name, 
    BADGENUMBER as badge,
    LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,checktype,
    Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
    from CHECKINOUT,USERINFO
    where CHECKINOUT.userid=USERINFO.userid
    and BADGENUMBER = '1693' and  checktype = 'I') as t1
left join
    (select CHECKINOUT.userid as chkuserid, checktype, LEFT(CONVERT(varchar, CHECKTIME, 23),11) as logindate,
        Format(cast(RIGHT(CHECKTIME,12) as datetime),'HH:mm:ss') AS timestamp
    from CHECKINOUT,USERINFO
    where CHECKINOUT.userid=USERINFO.userid
    and BADGENUMBER = '1693' and  checktype = '0')) as t2 on t2.chkuserid = t1.chkuserid
        and t2.logindate = t1.logindate

Thank you for this, I will use this as a base query for the future, I modified it a little bit due to syntax errors ````checktype = 'O') as t2 on t2.chkuserid =```` — pjustindaryll, Aug 13 '19 at 03:21
I'm really confused with ````LEFT JOIN````, I didn't know we can use it like this. — pjustindaryll, Aug 13 '19 at 03:22

LEFT JOIN Multiple rows from the same table | SQL SERVER 2012

1 Answers1