June 19, 2013 at 4:49 am
i use this syntax in sql server
when t.timein is null and l.date is not null then u.description
and make join like this
FROM attend_log) t
left join leaveinformation l on t.eid = l.eid and t.date = l.date
left join leavedescription u on l.lid = u.lid
but its not giving me the desired result
its giving me that result
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT
i want this type of result
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave
i join these tables
[LeaveDescription]
(
[LID] [int], <---------leave id
[Description] [varchar](50) <------------leave description
)
[LeaveInformation](
[CID] [int] NULL, <-----------company id
[BID] [int] NULL, <------------branch id
[EID] [int] NULL, <------------employee id
[Date] [datetime] NULL,
[LID] [int] NULL <-------------leave id
)
[ATTEND_LOG]
(
[EID] [int] NULL,<------------employeeid
[date] [datetime] NULL,
[timein] [datetime] NULL,
[timeout] [datetime] NULL,
[BID] [int] NULL, <------------------branch id
[EBID] [int] NULL,<--------------------employee branch id
[spendtime] [datetime] NULL,
[excessshort] [datetime] NULL,
[excess] [nvarchar](50) NULL
)
if there is a data in leave information table then it shows like this
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave
other wise shows like this
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT
immad
June 19, 2013 at 5:49 am
immaduddinahmed (6/19/2013)
i use this syntax in sql serverwhen t.timein is null and l.date is not null then u.description
and make join like this
FROM attend_log) t
left join leaveinformation l on t.eid = l.eid and t.date = l.date
left join leavedescription u on l.lid = u.lid
but its not giving me the desired result
its giving me that result
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT
i want this type of result
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave
i join these tables
[LeaveDescription]
(
[LID] [int], <---------leave id
[Description] [varchar](50) <------------leave description
)
[LeaveInformation](
[CID] [int] NULL, <-----------company id
[BID] [int] NULL, <------------branch id
[EID] [int] NULL, <------------employee id
[Date] [datetime] NULL,
[LID] [int] NULL <-------------leave id
)
[ATTEND_LOG]
(
[EID] [int] NULL,<------------employeeid
[date] [datetime] NULL,
[timein] [datetime] NULL,
[timeout] [datetime] NULL,
[BID] [int] NULL, <------------------branch id
[EBID] [int] NULL,<--------------------employee branch id
[spendtime] [datetime] NULL,
[excessshort] [datetime] NULL,
[excess] [nvarchar](50) NULL
)
if there is a data in leave information table then it shows like this
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave
other wise shows like this
date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT
Hi immaduddinahmed and welcome to the board. Please have a look at the posting guidelines and how to use code block and post sample data.
I am assuming that English is not your native language and your question is a bit hard to understand but I think you are asking for
Where there is a record in the leaveInformation table, return the leave reason but if there is not then return 'ABSENT'
If so then your query should look like this
SELECT
al.date,
al.eid,
al.timein,
al.timeout,
al.spendtime,
CASE
WHEN (al.date is not null and al.timein is null ) THEN coalesce(u.description,'ABSENT')
ELSE null
END as 'remarks'
FROM
attend_log al
LEFT JOIN
LeaveInformation l on l.empid = al.empid AND l.date = al.date
LEFT JOIN
LeaveDescription u on u.id = l.id
This shoud give you;
Every attendlog record
Remarks will be:
null if the attend date is blank (I am assuming that this doesn't happen and that the attend record is for expected attendance)
null if the attend date and timein are both filled in (in which case the employee was on shift as expected)
the leave reason if the date is there but the time is not and the leaveinformation record exists
'ABSENT' if the leaveinformation record does not exist
This is an observation and not part of your original question:
Your leaveinformation table has fields for branch and company whilst your attend table has fields for branch and employee branch. These fields are not specified in your join criteria. If they are required to uniquely identify an employee then they need to be included in the join. If they are not then it would indicate that the tables are not in 3rd normal form.
A branch would normally be a property of the employee rather than a specific attendance record: If an employee is permanently assigned to a branch then it should be a field on the employee table. If the employee can move around or work at more than one branch/company then use a separate x-ref table to normalise the data
Employee
(eid int,
empName nvarchar(50),
EmpPayrollID int,
etc...
)
Branch
(
ID int,
Description nvarchar(100),
etc..
)
EmpXBranch
(
ID int,
EmpID int,
BranchID int,
StartDate datetime not null,
EndDate datetime null
)
and then link the attendance record either to the employee record or to the empXbranch table by ID. either way there should be no need to put the branch on the attendace record.
You might just want to review your database table structures if you are still in design mode. It might look like more work to start with, but you will thank me for it later.
Obiron
June 19, 2013 at 6:26 am
no record added in the attend_log u have to calculate it and show absent into your data .that work is done .
problem is when data inserted on leave infomation table then when we run this procedure if there is a data in leave information table and matches leaveinformation date and attend_log date and matches leave information eid and attend_log eid then its show LID instead of absent in data other wise if no data in leave information then show absent.basically when employee get confirmed company give him a leaves but some employee are not confirmed so they didnt get leave what leave do when confirm employee absent user enter his data in leave information and he doesnot cut his salaray but unconfrim employee get absnet then user didnot enter his salary and he cut his salary then
i hope u under stand
immad
June 19, 2013 at 6:31 am
basically when employee get confirmed company give him a leaves but some employee are not confirmed so they didnt get leave what leave do when confirm employee absent user enter his data in leave information and he doesnot cut his salaray but unconfrim employee get absnet then user didnot enter his salary and he cut his salary then
Not only is there no full stops, but you are also missing some spaces!
Can you get someone with better English to help you post the problem because I am not following.
June 19, 2013 at 6:51 am
no absent record added in the attend_log.u have to calculate it and show absent into your data.that work is done u can see in the procedure .problem is when data inserted on leave infomation table then when we run the procedure if there is a data in leave information table that matches leave information date and attend_log date and matches leave information eid and attend_log eid then its show LID instead of absent other wise if no data in leave information table then show absent.
this is my procedure
ALTER procedure [dbo].[AT]
(
@empid nvarchar(50)
)
as
begin
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
E.CID,
E.BID,
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,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute- Time_Minutes), 0), 108) as excesshorttime,
case when (t4.minute - Time_Minutes) > 0 then 'Short'
when (t4.minute - Time_Minutes) < 0 then 'Excess'
else NULL end as ExcessShort,
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY'
--WHEN T.[Timein] is null AND t.[timeout] is null THEN T11.Description +' Leave'
ELSE ''
END AS Remarks,
case when t.BID = 2 and t.EBID = 1 then 'ITL 2'
when t.BID = 1 and t.EBID = 2 then 'ITL 1'
else ''
end
Comments
FROM (SELECT eid,[date]
FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-01-01','2013-01-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
end
this is a table structure
[LeaveInformation](
[CID] [int] NULL, <-----------company id
[BID] [int] NULL, <------------branch id
[EID] [int] NULL, <------------employee id
[Date] [datetime] NULL,
[LID] [int] NULL <-------------leave id
)
[ATTEND_LOG]
(
[EID] [int] NULL,<------------employeeid
[date] [datetime] NULL,
[timein] [datetime] NULL,
[timeout] [datetime] NULL,
[BID] [int] NULL, <------------------branch id
[EBID] [int] NULL,<--------------------employee branch id
[spendtime] [datetime] NULL,
[excessshort] [datetime] NULL,
[excess] [nvarchar](50) NULL
)
immad
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply