Hello Everyone,
I have created report for timesheet. This run perfect but I get one issu in that.
suppose
on 12/1/2016 I have fill up timesheet and
on 13/1/2016 i have fill up timesheet.
if i saw the report for 13/1/2016 then it will work perfectly,but when i select report for 12/1/2016 that time this date record and 13/1/2016 all record display.
I will send report aspx page and store procedure for it.
plz help for this solution.
ET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyTimeSheet_BasicReport]
@StartDate varchar(50) = '',
@EmployeeId INT = 0
AS
DECLARE @strQuery varchar(5000)
DECLARE @strCondition varchar(5000)
DECLARE @strOrderBy varchar(500)
SET @strCondition = ' WHERE 1 = 1 '
SET @strQuery=' SELECT CONVERT(VARCHAR, T.CreateDateTime,105) AS [LoginDate],
E.FullName,
T.TaskTimeIn,RIGHT(CONVERT(VARCHAR,T.TaskTimeIn,100),7) AS [TimeIn],
T.TaskTimeOut,RIGHT(CONVERT(VARCHAR,T.TaskTimeOut,100),7) AS [TimeOut],
T.TaskDescription,
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, TaskTimeIn, ISNULL(TaskTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [TimeSpent] ,
DATEDIFF(minute, TaskTimeIn, ISNULL(TaskTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [TimeSpentMinutes],
E.EmployeeId
FROM
DailyTimeSheetTrackers T
INNER JOIN Employees E ON E.EmployeeId = T.EmployeeId
'
if (@StartDate <> '')
SET @strCondition = @strCondition + ' AND CAST(CONVERT(VARCHAR ,T.CreateDateTime,101) AS DATE) >= CONVERT(VARCHAR ,'''+ @StartDate +''',101) '
if (@EmployeeId >0)
SET @strCondition = @strCondition + ' AND E.EmployeeId = ' + CAST(@EmployeeId AS varchar(10))
SET @strOrderBy = ' Order BY T.CreateDateTime,E.EmployeeId '
PRINT @strQuery + @strCondition + @strOrderBy
EXECUTE (@strQuery + @strCondition + @strOrderBy)