Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-05-21 : 01:20:08
|
i make a procedure in sql server 2005 and its giving me errorWarning: Null value is eliminated by an aggregate or other SET operation.(2 row(s) affected)(2 row(s) affected)Msg 205, Level 16, State 1, Procedure Procedure11, Line 186All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.my procedure is:ALTER procedure [dbo].[Procedure11] @EmpID VARCHAR(50), @FromDate DATETIME, @ToDate DATETIME, @Dept VARCHAR(50), @Remarks VARCHAR(50), @CID VARCHAR(50), @BID VARCHAR(50), @Grade VARCHAR(50)ASBEGINDECLARE @DDATE DATETIMESET @DDATE = (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)CREATE TABLE #P1( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50),SpendTimeDiffInSec NVARCHAR(50), EShortTimeinSEC NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), EShortTime NVARCHAR(50))INSERT INTO #P1SELECT * ,RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' + RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS SpendTimeFROM(Select --DISTINCTT.CID,T.BID,T4.BID EBID,T.EID,--T.DID,T4.DID,T.Date,T.TimeIn,T.IAttendID,T.TimeOut,T.OAttendID,T1.HR,T1.Min,T1.SpendTime,T3.SID,T3.Shift,T3.LTime,T3.HDTime,T3.EarlyArival,T3.ShiftTimeDiff,CASE --WHEN T.InBetween > 0 THEN ''--WHEN CONVERT(datetime, T1.SpendTime, 114) < CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN 'Short'--WHEN CONVERT(datetime, T1.SpendTime, 114) > CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN 'Excess'WHEN T1.SpendTime < T3.ShiftTimeDiff THEN 'Short'WHEN T1.SpendTime > T3.ShiftTimeDiff THEN 'Excess'ENDAS ExcShort,--CASE --WHEN CONVERT(datetime, T1.SpendTime, 114) < CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN CONVERT(datetime, T3.ShiftTimeDiff, 108) - CONVERT(datetime, T1.SpendTime, 108) --WHEN CONVERT(datetime, T1.SpendTime, 114) > CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN CONVERT(datetime, T1.SpendTime, 108) - CONVERT(datetime, T3.ShiftTimeDiff, 108) END--AS EShortTime,T1.TimeDiffInSec SpendTimeDiffInSec,CASE WHEN T1.TimeDiffInSec < T3.TimeDiffInSec THEN T3.TimeDiffInSec - T1.TimeDiffInSecWHEN T1.TimeDiffInSec > T3.TimeDiffInSec THEN T1.TimeDiffInSec - T3.TimeDiffInSec END AS TimeDiffInSec,T3.DutyHours,T.Remarks,T.Commentsfrom F2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) TLEFT OUTER JOIN FSpendTime (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.Date = T1.Date AND T.EID = T1.EIDLEFT OUTER JOIN Employee T4 ON T.EID = T4.EIDLEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T2 ON T4.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.Shift)DCREATE TABLE #Remark( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50),SpendTimeDiffInSec NVARCHAR(50), ExcShortTimeInSec NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), EShortTime NVARCHAR(50), rn INT, rnn INT)INSERT INTO #RemarkSELECT *, ISNULL(( SELECT TOP 1 COUNT(*) FROM #P1 t WHERE t.EmployeeCode=t1.EmployeeCode AND t.Date=t1.Date AND t1.[Time In]>t.[Time In] GROUP BY [Time In] ORDER BY [Time In] DESC ), 0) AS rn , ISNULL(( SELECT TOP 1 COUNT(*) FROM #P1 t WHERE T.EmployeeCode=T1.EmployeeCode AND T.Date=T1.Date --AND t1.[Time In]>t.[Time In] GROUP BY Date ORDER BY Date DESC ), 0) AS rnnFROM #P1 t1ORDER BY [Time In]SELECT T2.CID,T1.BID,T2.BID EBID,T2.DID,T5.DName Department,T6.DesignationID,T6.Name Designation,T.EID EmployeeCode,T2.EName Name,T7.CName EmpCompany,T8.BName EmpBranch,T9.BName AttBranch,T.Date,T1.IAttendID,case when t2.outsider = 1 then null else t1.[time in] end [time in],case when t2.outsider = 1 then null else T1.[Time Out] end [time out],T1.OAttendID,--T1.Shift,T10.SName Shift,CASEwhen t2.outsider= 1 then null WHEN T1.rn = 0 and t2.outsider = 0 THEN CONVERT(varchar(30), T1.Spend, 8) ELSE T1.spend END Spend,CASE when t2.outsider=1 then nullWHEN T1.rn = 0 THEN T1.ExcShort ELSE NULL END ExcShort,--CASE WHEN T1.rn = 0 THEN T1.EShortTime ELSE NULL END EShortTime,--CASE WHEN T1.rn = 0 THEN CONVERT(CHAR(5), T1.EShortTime,108) END EShortTime,--CASE WHEN T1.rn = 0 THEN LTRIM(RIGHT(CONVERT(VARCHAR(20), T1.EShortTime, 100), 9)) END EShortTime,--T1.EShortTime EShortTime,case when t2.outsider = 1 then nullelse CONVERT(CHAR(5), T1.EShortTime,108) end EShortTime,CASE WHEN T1.rn = 0 THEN T1.DiffHrs ELSE NULL END DiffHrs,CASE WHEN T1.rn = 0 THEN T1.DiffMin ELSE NULL END DiffMin,CASE WHEN T1.[Time Out] = NULL AND T1.Date = Convert (DATETIME,GETDATE(), 110) THEN NULLWHEN T1.rn = 0 AND T1.Date < GETDATE() THEN ShiftTimeDiff ELSE NULL END ShiftTimeDiff,CASE WHEN T1.rn = 0 THEN T1.DutyHours ELSE NULL END DutyHours,T2.LateExcemption,T2.Grade,T1.LTime,T1.HDTime,T1.EarlyArival,0 attstatus,T1.rn,T1.rnn,CASE WHEN T8.BName <> T9.BName THEN T9.BNameELSE '' END Comments,CASEWHEN T1.rn = 0 AND T1.Date > GETDATE() THEN NULLWHEN T10.SName = 'O' then 'OffDay'WHEN T1.[Time in] is null AND T11.Date IS NOT NULL THEN T11.Description +' Leave'WHEN T1.rn = 0 AND T2.LateExcemption = 1 AND CONVERT(char(8), [Time IN], 108) > CONVERT(char(8), LTime, 108) Then ''WHEN T1.rn = 0 AND T2.LateExcemption = 0 AND CONVERT(char(8), [Time IN], 108) > CONVERT(char(8), LTime, 108) Then 'Late'WHEN T1.rn = 0 AND T1.ExcShort = 'Short' AND CONVERT(varchar(5), CONVERT(CHAR(5), EShortTime,108), 8) > CONVERT(varchar(5), EarlyArival, 8) THEN 'Half day'WHEN (T1.rn is null OR rn = 0) and T1.[Time in] is null Then 'Absent'when t2.outsider = 1 and remarks ='' then 'Present'ELSE ''END Remarks,ExcShortTimeInSec,SpendTimeDiffInSec--((SpendTimeDiffInSec / 60) / 60) SpendTimeDiffInSecFROMFCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T--CalendarView TLEFT OUTER JOIN Employee T2 ON T.CID = T2.CID AND T.BID = T2.BID AND T.EID = T2.EIDLEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T10 ON T2.EID = T10.EID AND DATEPART(YYYY,T.DATE) = T10.Year AND DATEPART(MM,T.DATE) = T10.Month AND DATEPART(DD,T.DATE) = T10.DayLEFT OUTER JOIN #Remark T1 ON T.EID = T1.EmployeeCode AND T.Date = T1.DateLEFT OUTER JOIN Department T5 ON T2.CID = T5.CID AND T2.BID = T5.BID AND T2.DID = T5.DIDLEFT OUTER JOIN Designation T6 ON T2.CID = T6.CID AND T2.BID = T6.BID AND T2.DESIGNATIONID = T6.DESIGNATIONIDLEFT OUTER JOIN Company T7 ON T2.CID = T7.CIDLEFT OUTER JOIN Branch T8 ON T2.CID = T8.CID AND T2.BID = T8.BIDLEFT OUTER JOIN Branch T9 ON T2.CID = T9.CID AND T1.BID = T9.BIDLEFT OUTER JOIN FLeaves (@EmpID,@FromDate,@ToDate,@CID,@BID) T11 ON T.Date = T11.Date AND T2.CID = T11.CID AND T2.BID = T11.BID AND T2.EID = T11.EIDWhereT.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)AND T.Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(T2.DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR T2.CID = @CID)AND (ISNULL(@BID,'')='' OR T2.BID = @BID)AND (ISNULL(@Grade,'')='' OR T2.Grade = @Grade)UNION ALLSELECT * FROM TRANSWHEREDate BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR CID = @CID)AND (ISNULL(@BID,'')='' OR EBID = @BID)AND (ISNULL(@Grade,'')='' OR Grade = @Grade)ORDER BY EmployeeCode, Date, [Time In]ENDplease help me.thank youimmad uddin ahmed |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-21 : 01:29:11
|
Error message itself represents that number of columns in the both SELECT statements should be same whenever there is SET Operators(such as UNION ALL, UNION, INTERSECT.....etc)--see the below partUNION ALLSELECT * FROM TRANS -- You replace this * symbol with column names with respect to the first SELECT statementWHEREDate BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR CID = @CID)AND (ISNULL(@BID,'')='' OR EBID = @BID)AND (ISNULL(@Grade,'')='' OR Grade = @Grade)ORDER BY EmployeeCode, Date, [Time In] --Simple illustrationSELECT col1, col2, col3 -- Number of columns in both SELECTs should be Same and also the same data types FROM Table1UNION ALLSELECT Column1, column2, column3FROM Table2--Chandu |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-21 : 01:46:01
|
"Warning: Null value is eliminated by an aggregate or other SET operation" you can eliminate this warning message by using SET ANSI_WARNINGS OFFJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-05-21 : 02:36:20
|
Dear sir,i change a procedure just like u told me but i am still getting errorWarning: Null value is eliminated by an aggregate or other SET operation.(2 row(s) affected)(2 row(s) affected)Msg 205, Level 16, State 1, Procedure Procedure11, Line 194All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.this is my change procedure:ALTER procedure [dbo].[Procedure11] @EmpID VARCHAR(50), @FromDate DATETIME, @ToDate DATETIME, @Dept VARCHAR(50), @Remarks VARCHAR(50), @CID VARCHAR(50), @BID VARCHAR(50), @Grade VARCHAR(50)ASBEGINDECLARE @DDATE DATETIMESET @DDATE = (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)CREATE TABLE #P1( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50),SpendTimeDiffInSec NVARCHAR(50), EShortTimeinSEC NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), InBetween INT, EShortTime NVARCHAR(50))INSERT INTO #P1SELECT * ,RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' + RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS SpendTimeFROM(Select --DISTINCTT.CID,T.BID,T4.BID EBID,T.EID,--T.DID,T4.DID,T.Date,T.TimeIn,T.IAttendID,T.TimeOut,T.OAttendID,T1.HR,T1.Min,T1.SpendTime,T3.SID,T3.Shift,T3.LTime,T3.HDTime,T3.EarlyArival,T3.ShiftTimeDiff,CASE --WHEN T.InBetween > 0 THEN ''--WHEN CONVERT(datetime, T1.SpendTime, 114) < CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN 'Short'--WHEN CONVERT(datetime, T1.SpendTime, 114) > CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN 'Excess'WHEN T1.SpendTime < T3.ShiftTimeDiff THEN 'Short'WHEN T1.SpendTime > T3.ShiftTimeDiff THEN 'Excess'ENDAS ExcShort,--CASE --WHEN CONVERT(datetime, T1.SpendTime, 114) < CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN CONVERT(datetime, T3.ShiftTimeDiff, 108) - CONVERT(datetime, T1.SpendTime, 108) --WHEN CONVERT(datetime, T1.SpendTime, 114) > CONVERT(datetime, T3.ShiftTimeDiff, 114) THEN CONVERT(datetime, T1.SpendTime, 108) - CONVERT(datetime, T3.ShiftTimeDiff, 108) END--AS EShortTime,T1.TimeDiffInSec SpendTimeDiffInSec,CASE WHEN T1.TimeDiffInSec < T3.TimeDiffInSec THEN T3.TimeDiffInSec - T1.TimeDiffInSecWHEN T1.TimeDiffInSec > T3.TimeDiffInSec THEN T1.TimeDiffInSec - T3.TimeDiffInSec END AS TimeDiffInSec,T3.DutyHours,T.Remarks,T.Comments,T.InBetweenfrom F2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) TLEFT OUTER JOIN FSpendTime (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.Date = T1.Date AND T.EID = T1.EID AND T.InBetween = T1.InBetweenLEFT OUTER JOIN Employee T4 ON T.EID = T4.EIDLEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T2 ON T4.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.ShiftWHERET.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC))DCREATE TABLE #Remark( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50),SpendTimeDiffInSec NVARCHAR(50), ExcShortTimeInSec NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), InBetween INT, EShortTime NVARCHAR(50), rn INT, rnn INT)INSERT INTO #RemarkSELECT *, ISNULL(( SELECT TOP 1 COUNT(*) FROM #P1 t WHERE t.EmployeeCode=t1.EmployeeCode AND t.Date=t1.Date AND t1.[Time In]>t.[Time In] GROUP BY [Time In] ORDER BY [Time In] DESC ), 0) AS rn , ISNULL(( SELECT TOP 1 COUNT(*) FROM #P1 t WHERE T.EmployeeCode=T1.EmployeeCode AND T.Date=T1.Date --AND t1.[Time In]>t.[Time In] GROUP BY Date ORDER BY Date DESC ), 0) AS rnnFROM #P1 t1ORDER BY [Time In]SELECT T2.CID,T1.BID,T2.BID EBID,T2.DID,T5.DName Department,T6.DesignationID,T6.Name Designation,T.EID EmployeeCode,T2.EName Name,T7.CName EmpCompany,T8.BName EmpBranch,T9.BName AttBranch,T.Date,--case when t2.outsider = 1 then null else t1.[time in] end [timein], --case when t2.outsider = 1 then null else T1.[Time Out] end [timeout],t1.[time in],T1.[Time Out],T1.IAttendID,T1.OAttendID,--T1.Shift,T10.SName Shift,CASEwhen t2.outsider=1 then nullWHEN InBetween > 0 and Remarks NOT Like 'Official%' THEN CONVERT(varchar(30), T1.Spend, 8)WHEN INBETWEEN = 0 AND RN = 0 THEN CONVERT(varchar(30), T1.Spend, 8)WHEN rn = 0 and t2.outsider = 0 THEN CONVERT(varchar(30), T1.Spend, 8)ELSE NULL END Spend,--CASE WHEN T1.rn = 0 THEN CONVERT(varchar(30), T1.Spend, 8) ELSE NULL END Spend,CASE when t2.outsider = 1 then nullWHEN T1.rn = 0 THEN T1.ExcShort ELSE NULL END ExcShort,--CASE WHEN T1.rn = 0 THEN T1.EShortTime ELSE NULL END EShortTime,--CASE WHEN T1.rn = 0 THEN CONVERT(CHAR(5), T1.EShortTime,108) END EShortTime,--CASE WHEN T1.rn = 0 THEN LTRIM(RIGHT(CONVERT(VARCHAR(20), T1.EShortTime, 100), 9)) END EShortTime,--T1.EShortTime EShortTime,CASE when t2.outsider=1 then nullWHEN T1.rn = 0 THEN CONVERT(CHAR(5), T1.EShortTime,108) ELSE NULL END EShortTime,--CONVERT(CHAR(5), T1.EShortTime,108) EShortTime ,CASE WHEN T1.rn = 0 THEN T1.DiffHrs ELSE NULL END DiffHrs,CASE WHEN T1.rn = 0 THEN T1.DiffMin ELSE NULL END DiffMin,CASE WHEN T1.[Time Out] = NULL AND T1.Date = Convert (DATETIME,GETDATE(), 110) THEN NULLWHEN T1.rn = 0 AND T1.Date < GETDATE() THEN ShiftTimeDiff ELSE NULL END ShiftTimeDiff,CASE WHEN T1.rn = 0 THEN T1.DutyHours ELSE NULL END DutyHours,T2.LateExcemption,T2.Grade,T1.LTime,T1.HDTime,T1.EarlyArival,0 attstatus,T1.rn,T1.rnn,CASE WHEN T8.BName <> T9.BName THEN T9.BNameELSE T1.Comments END Comments,CASEWHEN T1.rn = 0 AND T1.Date > GETDATE() THEN NULLWHEN IAttendID = 2 and OAttendID = 2 THEN T1.RemarksWHEN T10.SName = 'O' then 'OffDay'WHEN T1.[Time in] is null AND T11.Date IS NOT NULL THEN T11.Description +' Leave'WHEN T1.rn = 0 AND T2.LateExcemption = 1 AND CONVERT(char(8), [Time IN], 108) > CONVERT(char(8), LTime, 108) Then ''WHEN T1.rn = 0 AND T2.LateExcemption = 0 AND CONVERT(char(8), [Time IN], 108) > CONVERT(char(8), LTime, 108) Then 'Late'WHEN T1.rn = 0 AND T1.ExcShort = 'Short' AND CONVERT(varchar(5), CONVERT(CHAR(5), EShortTime,108), 8) > CONVERT(varchar(5), EarlyArival, 8) THEN 'Half day'WHEN (T1.rn is null OR rn = 0) and T1.[Time in] is null Then 'Absent'when remarks = '' and t2.outsider=1 then 'Present'ELSE ''END Remarks,ExcShortTimeInSec,SpendTimeDiffInSec,T1.InBetween--((SpendTimeDiffInSec / 60) / 60) SpendTimeDiffInSecFROMFCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T--CalendarView TLEFT OUTER JOIN Employee T2 ON T.CID = T2.CID AND T.BID = T2.BID AND T.EID = T2.EIDLEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T10 ON T2.EID = T10.EID AND DATEPART(YYYY,T.DATE) = T10.Year AND DATEPART(MM,T.DATE) = T10.Month AND DATEPART(DD,T.DATE) = T10.Day--LEFT OUTER JOIN #Remark T1 ON T.EID = T1.EmployeeCode AND T.Date = T1.DateLEFT OUTER JOIN #Remark T1 ON T.EID = T1.EmployeeCode AND T.Date = T1.Date AND T.BID = T1.EBIDLEFT OUTER JOIN Department T5 ON T2.CID = T5.CID AND T2.BID = T5.BID AND T2.DID = T5.DIDLEFT OUTER JOIN Designation T6 ON T2.CID = T6.CID AND T2.BID = T6.BID AND T2.DESIGNATIONID = T6.DESIGNATIONIDLEFT OUTER JOIN Company T7 ON T2.CID = T7.CIDLEFT OUTER JOIN Branch T8 ON T2.CID = T8.CID AND T2.BID = T8.BIDLEFT OUTER JOIN Branch T9 ON T2.CID = T9.CID AND T1.BID = T9.BIDLEFT OUTER JOIN FLeaves (@EmpID,@FromDate,@ToDate,@CID,@BID) T11 ON T.Date = T11.Date AND T2.CID = T11.CID AND T2.BID = T11.BID AND T2.EID = T11.EIDWhereT.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)ANDT.Date BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(T2.DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR T2.CID = @CID)AND (ISNULL(@BID,'')='' OR T2.BID = @BID)UNION ALLSELECT CID,BID,BID EBID,DID,Department,DesignationID,Name Designation,EmployeeCode,Name,EmpCompany,EmpBranch,AttBranch,Date,--case when t2.outsider = 1 then null else t1.[time in] end [timein], --case when t2.outsider = 1 then null else T1.[Time Out] end [timeout],[time in],[Time Out],IAttendID,OAttendID,--T1.Shift,Shift,Spend,ExcShort,EShortTime,DiffHrs,DiffMin,ShiftTimeDiff,DutyHours,LateExcemption,Grade,LTime,HDTime,EarlyArival,attstatus,rn,rnn,Comments,Remarks,ExcShortTimeInSec,SpendTimeDiffInSec,InBetween,outsiderFROM TRANSWHEREDate BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR CID = @CID)AND (ISNULL(@BID,'')='' OR EBID = @BID)AND (ISNULL(@Grade,'')='' OR Grade = @Grade)ORDER BY EmployeeCode, Date, [Time In]ENDimmad uddin ahmed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-21 : 02:48:30
|
InBetween ,outsiderFROM TRANSWHEREDate BETWEEN @FromDate and @ToDateAND (ISNULL(@EmpID,'')='' OR EmployeeCode = @EmpID)AND (ISNULL(@Dept,'')='' OR ',' + @Dept + ',' LIKE '%,' + CAST(DID AS varchar) + ',%')AND (ISNULL(@CID,'')='' OR CID = @CID)AND (ISNULL(@BID,'')='' OR EBID = @BID)AND (ISNULL(@Grade,'')='' OR Grade = @Grade)ORDER BY EmployeeCode, Date, [Time In]END--Chandu |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-05-21 : 03:53:34
|
Thank You My Problem is solve immad uddin ahmed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-21 : 06:00:16
|
quote: Originally posted by immad Thank You My Problem is solve immad uddin ahmed
Welcome--Chandu |
|
|
|
|
|