Sir my first problem about ABS function is solve
sir i change query u can see in bold fonts
but its give me error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
ALTER procedure [dbo].[query]
(
@empid nvarchar(50)
)
as
begin
select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(ss, [Timein], [Timeout])) as Time_In_Seconds
into #temp1 from attendlog
where eid=@empid
group by [date]
select
t.[date],
e.ename,
t.eid,
t5.dname,
d.name designation,
T4.shift,
t.[Timein] timein,
t.[Timeout]timeout,
--t4.ltime Latetime,
--t4.HDTime,
CONVERT(VARCHAR(8), DATEADD(ss, Time_In_Seconds, 0), 108) AS SpendTime,
CAST (ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) / 60 as varchar ) + ' hrs : ' +-- Hours ( + ':' separator )
CAST (ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend) ) % 60 as varchar ) + ' min' as excesshort,-- Minutes
CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short' END Excess,
case when convert(varchar(10),t.[Timein],108) > convert(varchar(10),t4.ltime,108) then 'Late'
when convert(varchar(10),t.[Timein],108) is null and convert(varchar(10),t.[Timeout],108) is null and shift <> 'O' then 'Absent'
when t4.shift = 'O' then 'OFFDAY'
when t4.HDTime > t.[Timeout] and Excess = 'Short' then 'HalfDay'
else '' end Remarks
FROM attendi t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
--left join froaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) r ON t.eid = r.eid
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where t.eid=@empid
order by t.[date]
DECLARE @excess varchar(6)
SET @excess =select CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short' end excess
update
attendlog
set
excessshort =
CAST ( ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST ( ABS( convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) % 60 as varchar )-- Minutes
,
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss, t.[Timein],t.[Timeout]),0),108)
,
Excess = @excess
from
attendi t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where t.eid=@empid
end
immad