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.