SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 05/21/2013 :  01:20:08  Show Profile  Reply with Quote
i make a procedure in sql server 2005 and its giving me error


Warning: 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 186
All 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)
AS
BEGIN
DECLARE @DDATE DATETIME
SET @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 #P1


SELECT
* ,
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 SpendTime
FROM
(
Select
--DISTINCT
T.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'

END
AS 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.TimeDiffInSec
WHEN T1.TimeDiffInSec > T3.TimeDiffInSec
THEN T1.TimeDiffInSec - T3.TimeDiffInSec
END AS TimeDiffInSec,
T3.DutyHours,
T.Remarks,
T.Comments
from
F2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T
LEFT OUTER JOIN FSpendTime (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.Date = T1.Date AND T.EID = T1.EID
LEFT OUTER JOIN Employee T4 ON T.EID = T4.EID
LEFT 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.Day
LEFT OUTER JOIN ShiftView T3 ON T2.SName = T3.Shift
)D


CREATE 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 #Remark
SELECT
*,
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 rnn

FROM
#P1 t1

ORDER 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,
CASE
when 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 null
WHEN 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 null
else 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 NULL
WHEN 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.BName
ELSE '' END Comments,

CASE
WHEN T1.rn = 0 AND T1.Date > GETDATE() THEN NULL
WHEN 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) SpendTimeDiffInSec
FROM
FCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T
--CalendarView T
LEFT OUTER JOIN Employee T2 ON T.CID = T2.CID AND T.BID = T2.BID AND T.EID = T2.EID
LEFT 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.Date
LEFT OUTER JOIN Department T5 ON T2.CID = T5.CID AND T2.BID = T5.BID AND T2.DID = T5.DID
LEFT OUTER JOIN Designation T6 ON T2.CID = T6.CID AND T2.BID = T6.BID AND T2.DESIGNATIONID = T6.DESIGNATIONID
LEFT OUTER JOIN Company T7 ON T2.CID = T7.CID
LEFT OUTER JOIN Branch T8 ON T2.CID = T8.CID AND T2.BID = T8.BID
LEFT OUTER JOIN Branch T9 ON T2.CID = T9.CID AND T1.BID = T9.BID
LEFT 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.EID


Where
T.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)
AND T.Date BETWEEN @FromDate and @ToDate
AND (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 ALL

SELECT * FROM TRANS
WHERE
Date BETWEEN @FromDate and @ToDate
AND (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

please help me.
thank you

immad uddin ahmed

Edited by - immad on 05/21/2013 01:23:01

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 05/21/2013 :  01:29:11  Show Profile  Reply with Quote
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 part

UNION ALL
SELECT * FROM TRANS    -- You replace this * symbol with column names with respect to the first SELECT statement
WHERE
Date BETWEEN @FromDate and @ToDate
AND (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 illustration
SELECT col1, col2, col3 -- Number of columns in both SELECTs should be Same and also the same data types
FROM Table1
UNION ALL
SELECT Column1, column2, column3
FROM Table2

--
Chandu
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 05/21/2013 :  01:46:01  Show Profile  Visit jackv's Homepage  Reply with Quote
"Warning: Null value is eliminated by an aggregate or other SET operation" you can eliminate this warning message by using SET ANSI_WARNINGS OFF






Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 05/21/2013 :  02:36:20  Show Profile  Reply with Quote
Dear sir,

i change a procedure just like u told me but i am still getting error



Warning: 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 194
All 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)
AS
BEGIN
DECLARE @DDATE DATETIME
SET @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 #P1


SELECT
* ,
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 SpendTime
FROM
(
Select
--DISTINCT
T.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'

END
AS 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.TimeDiffInSec
WHEN T1.TimeDiffInSec > T3.TimeDiffInSec
THEN T1.TimeDiffInSec - T3.TimeDiffInSec
END AS TimeDiffInSec,

T3.DutyHours,
T.Remarks,
T.Comments,
T.InBetween
from
F2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T
LEFT OUTER JOIN FSpendTime (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.Date = T1.Date AND T.EID = T1.EID AND T.InBetween = T1.InBetween
LEFT OUTER JOIN Employee T4 ON T.EID = T4.EID
LEFT 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.Day
LEFT OUTER JOIN ShiftView T3 ON T2.SName = T3.Shift
WHERE
T.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)
)D


CREATE 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 #Remark
SELECT
*,
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 rnn

FROM
#P1 t1


ORDER 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,

CASE
when t2.outsider=1 then null
WHEN 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 null
WHEN 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 null
WHEN 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 NULL
WHEN 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.BName
ELSE T1.Comments END Comments,

CASE
WHEN T1.rn = 0 AND T1.Date > GETDATE() THEN NULL
WHEN IAttendID = 2 and OAttendID = 2 THEN T1.Remarks
WHEN 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) SpendTimeDiffInSec
FROM
FCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T
--CalendarView T
LEFT OUTER JOIN Employee T2 ON T.CID = T2.CID AND T.BID = T2.BID AND T.EID = T2.EID
LEFT 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.Date
LEFT OUTER JOIN #Remark T1 ON T.EID = T1.EmployeeCode AND T.Date = T1.Date AND T.BID = T1.EBID

LEFT OUTER JOIN Department T5 ON T2.CID = T5.CID AND T2.BID = T5.BID AND T2.DID = T5.DID
LEFT OUTER JOIN Designation T6 ON T2.CID = T6.CID AND T2.BID = T6.BID AND T2.DESIGNATIONID = T6.DESIGNATIONID
LEFT OUTER JOIN Company T7 ON T2.CID = T7.CID
LEFT OUTER JOIN Branch T8 ON T2.CID = T8.CID AND T2.BID = T8.BID
LEFT OUTER JOIN Branch T9 ON T2.CID = T9.CID AND T1.BID = T9.BID
LEFT 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.EID


Where

T.Date > (SELECT TOP 1 DATE FROM TRANS order by DATE DESC)
AND
T.Date BETWEEN @FromDate and @ToDate
AND (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 ALL

SELECT
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,
outsider

FROM TRANS
WHERE
Date BETWEEN @FromDate and @ToDate
AND (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

immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 05/21/2013 :  02:48:30  Show Profile  Reply with Quote
InBetween ,
outsider


FROM TRANS
WHERE
Date BETWEEN @FromDate and @ToDate
AND (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
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 05/21/2013 :  03:53:34  Show Profile  Reply with Quote
Thank You My Problem is solve

immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 05/21/2013 :  06:00:16  Show Profile  Reply with Quote
quote:
Originally posted by immad

Thank You My Problem is solve

immad uddin ahmed


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000