immad
Posting Yak Master
230 Posts |
Posted - 2013-05-27 : 05:08:28
|
it gives me this error Conversion failed when converting the varchar value '790-------' to data type int.My procedure are like thisALTER procedure [dbo].[SearchforEdit] @EmpID NVARCHAR(50), @FromDate DATETIME, @ToDate DATETIME, @CID NVARCHAR(50), @BID NVARCHAR(50), @DEPT NVARCHAR(50)ASBEGINSELECT DISTINCTRTRIM(T1.EID),CONVERT(VARCHAR(10),Date,105) Date,Date Date1,TimeIn,TimeOut,T.Remarks,T.Comments,T1.ENAME,T2.DName,T3.NAme Designation,T4.CID,T4.CName,T5.BID,T5.BNameFROM(SELECTEID,Date,TimeIn,TimeOut,Remarks,CommentsFROMFE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept)Where(InCType = 2 OR OutCType = 2 OR Remarks = 'Absent')AND Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR EID = @EmpID)AND (ISNULL(@CID,'')='' OR CID = @CID)AND (ISNULL(@BID,'')='' OR BID = @BID)UNIONSELECT * FROM(SELECTT1.EID,T1.[Date],T2.TimeOut AS Timein,T1.TimeIn AS TimeOut,T1.Remarks,T1.CommentsFROMFE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1LEFT OUTER JOIN FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T2 ON T2.EID = T1.EID AND T2.TimeOut <> T1.TimeIN AND T2.Date = T1.Date AND T2.rn = T1.rn - 1WhereT1.TimeIn IS NOT NULLAND T1.Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR T1.EID = @EmpID)AND (ISNULL(@CID,'')='' OR T1.CID = @CID)AND (ISNULL(@BID,'')='' OR T1.BID = @BID))DWhereTimeIn IS NOT NULLUNIONSELECTRTRIM(T1.EID),Date,Case When rn = 1 AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTime,108) THEN DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(8),Date,112) As datetime)), Cast(CONVERT(VARCHAR(8),ShiftTimeIn,108) as datetime))ELSE TimeIn END TimeIn,Case When rn = 1 AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTime,108) THEN TimeIn ELSE NULL END TimeOut,Remarks,CommentsFROMFE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) TLEFT OUTER JOIN Employee T1 ON T.EID = T1.EIDLEFT OUTER JOIN Designation T2 ON T1.CID = T2.CID AND T1.BID = T2.BID AND T1.DesignationID = T2.DesignationIDWhere(InCType = 0) AND (T2.LateExcemption = 0 AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTIME,108))AND CONVERT(VARCHAR(8),T.TimeIn,108) > CONVERT(VARCHAR(8),T.LTIME,108) AND rn = 1AND T.Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)AND (ISNULL(@CID,'')='' OR T.CID = @CID)AND (ISNULL(@BID,'')='' OR T.BID = @BID)UNIONSELECTRTRIM(T1.EID),Date,Case When rn = 1 AND CONVERT(VARCHAR(8),TimeOut,108) < CONVERT(VARCHAR(8),ShiftTimeOut,108) THEN TimeOutELSE NULLEND TimeIn,Case When rn = 1 AND CONVERT(VARCHAR(8),TimeOut,108) < CONVERT(VARCHAR(8),ShiftTimeOut,108) THEN ShiftTimeOutELSE NULL END TimeOut,Remarks,CommentsFROMFE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) TLEFT OUTER JOIN Employee T1 ON T.EID = T1.EIDLEFT OUTER JOIN Designation T2 ON T1.CID = T2.CID AND T1.BID = T2.BID AND T1.DesignationID = T2.DesignationIDWhere(InCType = 0) AND CONVERT(VARCHAR(8),T.TimeOut,108) < CONVERT(VARCHAR(8),T.ShiftTimeOut,108) AND rn = 1AND T.Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)AND (ISNULL(@CID,'')='' OR T.CID = @CID)AND (ISNULL(@BID,'')='' OR T.BID = @BID))TLEFT OUTER JOIN Employee T1 ON T.EID = T1.EIDLEFT OUTER JOIN Department T2 ON T1.DID = T2.DIDLEFT OUTER JOIN Designation T3 ON T1.CID = T3.CID AND T1.BID = T3.BID AND T1.DesignationID = T3.DesignationIDLEFT OUTER JOIN Company T4 ON T1.CID = T4.CIDLEFT OUTER JOIN Branch T5 ON T1.BID = T5.BIDEND----------------------------------------------------------------my function is like thisALTER FUNCTION [dbo].[FE4](@CID int,@BID int,@EmpID int,@FromDate Datetime,@ToDate Datetime,@Dept VARCHAR)RETURNS TABLEASRETURN SELECT--RTRIM(replace(T1.EID, ' ', '')) EID,RTRIM(T1.EID) EID,T2.CID,T2.BID,T1.Date,T1.TimeIn,T1.INCTYPE,T1.TimeOut,T1.OUTCTYPE,T1.ShiftTimeDiff,T1.DutyHours,T1.ShiftTimeIn,T1.ShiftTimeOut,T1.LTime,T1.HDTime,T1.EarlyArival,T1.Remarks,T1.Comments,rn = ROW_NUMBER() OVER (PARTITION BY RTRIM(T1.EID),DATEADD(dd, 0, DATEDIFF(dd, 0, T1.Date))ORDER BY T1 .TimeIN )FROM FE3 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1LEFT OUTER JOIN Employee T2 ON T1.EID = T2.EID------------------------------------second function is like thisALTER FUNCTION [dbo].[FE3](@CID int,@BID int,@EmpID int,@FromDate Datetime,@ToDate Datetime,@Dept VARCHAR)RETURNS TABLEASRETURNSELECTT.CID,T.BID,T.EID,T.Date,T1.TimeIN,T1.INCTYPE,T1.TimeOut,T1.OUTCType,CASEWHEN T2.SName = 'O' THEN 'Off Day'WHEN T1.Timein IS NULL THEN 'Absent'ELSE T1.RemarksEND Remarks,T1.Comments,T2.SName ShiftName,T3.TimeIn ShiftTimeIn,T3.TimeOut ShiftTimeOut,T3.LTime,T3.HDTime,T3.EarlyArival,T3.ShiftTimeDiff,T3.DutyHoursFROMFCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) TLEFT OUTER JOIN FE2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.EID = T1.EID AND T.Date = T1.DateLEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T2 on T.EID = T2.EID AND DATEPART(YYYY,T.DATE) = T2.Year AND DATEPART(MM,T.DATE) = T2.Month AND DATEPART(DD,T.DATE) = T2.DayLEFT OUTER JOIN ShiftView T3 ON T2.SName = T3.Shiftcan any one tell me what is the problemimmad uddin ahmed |
|