I am having a hard time figuring this out in MS SQL. Hope you can guide me with this.

Let me tell you my story:

I have a daily attendance report for merchandisers. Within a day, the merchandisers are moving from one store to another.

So they have an application where they would time in, in the morning, check in when they came to a store, check out to that store, and then time out in the evening.

Okay, now I have a portal where I would gather all the sent data from the application.

Currently, I can retrieve the data, it is fine. My current report looks like this:

diser | store  |  date       |time in | time out | check in| check out ------------------------------------------------------------------------ John  | Store 1| 02/06/2018  | 7:00AM | 5:00PM   | 8:00AM  | 9:00AM ------------------------------------------------------------------------ John  | Store 2| 02/06/2018  | 7:00AM | 5:00PM   | 10:00AM | 11:00AM ------------------------------------------------------------------------ John  | Store 3| 02/06/2018  | 7:00AM | 5:00PM   | 1:00PM  | 2:00PM 

I will post the screenshot of my table in the portal.

But my boss wants to see the attendance for today early in the morning. For example, if the merchandiser only has a time in for today, he wants to see that already. My current report will not show if the merchandiser didn’t complete his/her attendance for today.

Here is what I have tried:

select  ca.SGrp as salesGroupCode,     ca.SOffc as salesOfficeCode,     ca.SDst as salesDistrictCode,     ca.CustCode as customerCode,     ca.CustNm as customer,     e.Comp as agency,     e.LName + ', ' + e.FName as diser,     CONVERT(date, a.DtFrom) as date,     convert(varchar,cast(a.DtFrom as time(0)), 109) as timeIn,     convert(varchar,cast(b.DtFrom  as time(0)), 109) as timeOut,     convert(varchar,cast(c.DtFrom  as time(0)), 109) as checkIn,     convert(varchar,cast(d.DtFrom  as time(0)), 109) as checkOut,     overall_count = COUNT(*) OVER()  from    BigESentData.dbo.tbl_sentAttendance as a      left join BigESentData.dbo.tbl_sentAttendance b     on  b.CellNum = a.CellNum and         convert(varchar, a.DtFrom, 111) = convert(varchar, b.DtFrom, 111) and         b.TransTyp = 6      left join BigESentData.dbo.tbl_sentAttendance c     on  b.CellNum = a.CellNum and         c.DtFrom between a.DtFrom and isnull(b.DtFrom, @dateFrom) and         c.TransTyp = 7     left join BigEMasterData.dbo.tbl_Customers as ca     on c.CustCode = ca.CustCode       left join BigESentData.dbo.tbl_sentAttendance d     on  b.CellNum = a.CellNum and         d.DtFrom between a.DtFrom and isnull(b.DtFrom, @dateFrom) and         c.CustCode = d.CustCode and         d.TransTyp = 8      left join BigESentData.dbo.tbl_sentRegistration e     on a.CellNum = e.CellNum      inner join BigEUsers.dbo.user_role_area as f     on ca.SDst = f.area_id      inner join BigEUsers.dbo.users as g     on f.role_id = g.role_id   where   convert(varchar, a.DtFrom, 111) between @dateFrom and @dateTo AND    ca.SGrp LIKE ISNULL('%' + @salesGroupCode + '%', ca.SGrp) AND    ca.SOffc LIKE ISNULL('%' + @salesOfficeCode + '%', ca.SOffc) AND    ca.SDst LIKE ISNULL('%' + @salesDistrictCode + '%', ca.SDst) AND    a.TransTyp= 5 AND    g.id = @userId  order by a.DtFrom desc, diser asc, timeIn asc  OFFSET @start ROWS FETCH NEXT @pageSize ROWS ONLY; 

Here is the screenshot of my table in the portal:

How can I achieve this? Hope you could help me. I already used left joins. Maybe I am designing it wrong. Any tips and suggestion would be appreciated very much. Thankyou.