I have created a view to identify the shift adjustments of our employee database.
Here when I login with the admin login and run the query i get 27 records. when i login with a read-only login and run the query i only get 1 record.
any suggestion why this is happening?
this is my query
SELECT dbo.DailyAttendance.EmployeeCode AS EMP_NUMBER, dbo.ShiftAdjustment.Date AS RSD_DATE, dbo.ShiftDefinition.ShiftName AS SFT_CODE, CONVERT(VARCHAR(5), dbo.ShiftDetail.InTime, 108) AS SFT_TIMEIN, CONVERT(varchar(5), dbo.ShiftDetail.OutTime, 108) AS SFT_TIMEOUT, dbo.DailyAttendance.Date AS IOT_INDATE, CONVERT(varchar(5), dbo.DailyAttendance.InTime, 108) AS IOT_INTIME, dbo.DailyAttendance.OutDate AS IOT_OUTDATE, CONVERT(varchar(5), dbo.DailyAttendance.OutTime, 108) AS IOT_OUTTIME, dbo.ShiftDetail.LeaveDays AS SFT_LEAVEAMOUNT, CASE WHEN dbo.ShiftDetail.Midnight = 'False' THEN 0 ELSE 1 END AS SFT_MIDNIGHT FROM dbo.DailyAttendance INNER JOIN dbo.ShiftAdjustment ON dbo.DailyAttendance.EmployeeCode = dbo.ShiftAdjustment.EmployeeCode AND dbo.DailyAttendance.Date = dbo.ShiftAdjustment.Date AND YEAR(dbo.ShiftAdjustment.Date) = YEAR(GETDATE()) AND DATEPART(wk, dbo.ShiftAdjustment.Date) = DATEPART(wk, GETDATE()) + CASE WHEN datepart(dw, getdate()) <= 2 THEN - 1 ELSE 0 END INNER JOIN dbo.Shift WITH (nolock) ON dbo.ShiftAdjustment.ShiftCode = dbo.ShiftDefinition.ShiftCode INNER JOIN dbo.ShiftDetail ON dbo.DailyAttendance.DayType = dbo.ShiftDetail.DayType AND dbo.ShiftAdjustment.ShiftCode = dbo.ShiftDetail.ShiftCode WHERE (dbo.ShiftAdjustment.EmployeeCode IS NOT NULL) AND (YEAR(dbo.DailyAttendance.Date) = YEAR(GETDATE())) AND (DATEPART(wk, dbo.DailyAttendance.Date) = DATEPART(wk, GETDATE()) + CASE WHEN datepart(dw, getdate()) <= 2 THEN - 1 ELSE 0 END)