SQL Server Performance

Triggers

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Jun 18, 2013.

  1. immad Member

    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from attend_log
    where eid = @empid
    group by [date]


    select
    t2.[date],
    t2.eid,
    e.ename Employeename,
    case when datediff(dd,0,t2.date)%7 < 6 then t4.shift else 'O' end as shift,
    d.name Designation,
    t5.Dname Department,
    t.[Timein] as Timein,
    t.[Timeout] as Timeout,
    CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,
    CASE WHEN Seq=1 and(t4.minute - Time_Minutes) > 0 Then '- ' else '+ ' end
    +CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) as ExcessShortTime,
    CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] and spendtime is not null THEN 'Excess' else 'Short' END Excess,
    case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
    when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late'
    when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
    else '' end Remarks,
    case when (t4.minute - Time_Minutes) >= 120 then 'HALF DAY'
    else '' end HalfDay


    FROM (SELECT eid,[date]
    FROM (select distinct eid from attend_log)a
    cross join dbo.calendartable('2013-05-01','2013-05-31',0,0)b
    ) t2
    left join (select row_number() over (partition by [date],eid ORDER BY timein ASC) AS Seq
    ,sum(DATEDIFF(minute, [Timein], [Timeout])) OVER (PARTITION BY [date],eid) AS Time_Minutes
    ,*
    FROM attend_log) t
    on t.[date] = t2.[date]
    and t.eid = t2.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 t2.eid = @empid
    order by t2.[date], t.[Timein]


    update
    ATTEND_LOG
    set


    excessshort =


    case when (t4.minute - Time_Minutes) > 0 Then ' ' else ' ' end
    + CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108)

    ,
    SpendTime =
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108)


    from


    ATTEND_LOG 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

    how to make a procedure
  2. FrankKalis Moderator

    Well, in its most basic form, put a CREATE PROCEDURE <your fancy schema name >.< ypur fancy procedure name > AS .... at the top of your statement. But then you probably want some error handling as well, so have a look at BEGIN TRY in Books Online.
  3. immad Member

    I GET IT

Share This Page