Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 LEAVE

Author  Topic 

immad
Posting Yak Master

230 Posts

Posted - 2013-06-19 : 06:45:10

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 shows like this


date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT


immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 06:54:15
What is the problem here.. I'm not able to understand the issue..
Can you explain clearly with some sample data and expected output...

--
Chandu
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-19 : 07:04:11
well i am making a attendance report some times employee get absent so we insert his leave in leave information table

like this

cid---bid-----eid---------date----------------------------------lid
1-----1------17074-----2013-01-07 00:00:00.000--------1



write now data is like

date-----------------------------eid-----------timein------timeout-----spendtime-----remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL---------absent


becouse i didnt link these table with the query i try to but its not showing me this result


date-----------------------------eid-----------timein------timeout-----spendtime-----remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL-----------1



its still show me this result


date-----------------------------eid-----------timein------timeout-----spendtime-----remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL---------absent

immad uddin ahmed
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 07:50:42
quote:

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 shows like this


date-----------------------------eid---------timein-----timeout---spendtime---remarks--
2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT



That means a record is added to the attend_log table even if an employee is on leave but just only with Date information and no time in/out info. on the other hand if an employee is absent, even then a record is added for that particular eID and bID but no information for Date,timein,timeout fields? If so, following is how your approach might need to be

SELECT
A.date
,A.eID,
CASE
When B.date is not null and B.TimeIn is null and B.timeout is null then 'Casual Leave'
WHEN b.Date is null and B.timein is null and b.timeout is null then 'Absent'
END As Remarks
FROM LeaveInformation A
INNER JOIN ATTEND_LOG B on A.eID=B.eID and A.bID=B.bID
WHERE TimeIn is NULL and TimeOut IS NULL


Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 08:03:32
[code]--May be this?
SELECT
li.DATE
,a.EID
,CASE when a.timein is null and li.date is not null then lu.description END AS LeaveDesc
FROM [ATTEND_LOG] a
JOIN [LeaveInformation] li ON a.[BID] = li.[BID] AND a.EID = li.EID
JOIN [LeaveDescription] ld ON li.[LID] = ld.[LID][/code]

--
Chandu
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-19 : 08:22:24
no record added in the attend_log u have to calculate it and show absent into your data .that work is done u can see the red line.
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 dataother 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 .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(540 - 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 uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 10:31:56
i cant even see leaveinformation table in your query. where are you using it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-20 : 01:25:07


ALTER procedure [dbo].[p]
(
@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
t2.[date],
t2.eid,
case when datediff(dd,0,t2.date)%7 < 6 then t4.shift else 'O' end as shift,
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 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 t9.date=t.date and t9.eid = t.eid then t10.DESCRIPTION
ELSE ''
END AS Remarks


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 outer join leaveinformation t9 on t.eid = t9.eid and t.date = t9.date
left outer join LEAVEDESCRIPTION t10 on t9.Lid = t10.Lid
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
where t2.eid = @empid
order by t2.[date], t.[Timein]

end




result showing like this



date---------------------------eid--------shift------timein--------------------------timeout---------------spendtime---remarks
2013-01-01 00:00:00.000--17074-----G----2013-06-19 08:39:00.000--2013-06-19 18:04:00.000---09:25:00----NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 08:42:00.000--2013-06-19 09:56:00.000---08:50:00----NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 10:09:00.000--2013-06-19 12:23:00.000------NULL-------NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 12:32:00.000--2013-06-19 13:14:00.000-------NULL------NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 13:30:00.000--2013-06-19 18:10:00.000-------NULL------NULL
2013-01-03 00:00:00.000--17074-----G----2013-06-19 08:40:00.000--2013-06-19 17:55:00.000------09:15:00---NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 08:32:00.000--2013-06-19 10:53:00.000------08:45:00---NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 11:09:00.000--2013-06-19 12:57:00.000--------NULL-----NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:06:00.000--2013-06-19 13:18:00.000--------NULL-----NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:45:00.000--2013-06-19 18:09:00.000--------NULL-----NULL
2013-01-05 00:00:00.000--17074-----G----2013-06-19 08:47:00.000--2013-06-19 18:01:00.000-------09:14:00---NULL
2013-01-06 00:00:00.000--17074-----O--------NULL-------------------------NULL --------------------------NULL----OFF DAY
2013-01-07 00:00:00.000--17074----NULL------------NULL-----------------NULL --------------------------NULL----ABSENT


I WANT THIS TYPE OF RESULT if data inserted in leave information table
other wise show absent



date---------------------------eid--------shift------timein--------------------------timeout---------------spendtime---remarks
2013-01-01 00:00:00.000--17074-----G----2013-06-19 08:39:00.000--2013-06-19 18:04:00.000---09:25:00----NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 08:42:00.000--2013-06-19 09:56:00.000---08:50:00----NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 10:09:00.000--2013-06-19 12:23:00.000------NULL-------NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 12:32:00.000--2013-06-19 13:14:00.000-------NULL------NULL
2013-01-02 00:00:00.000--17074-----G----2013-06-19 13:30:00.000--2013-06-19 18:10:00.000-------NULL------NULL
2013-01-03 00:00:00.000--17074-----G----2013-06-19 08:40:00.000--2013-06-19 17:55:00.000------09:15:00---NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 08:32:00.000--2013-06-19 10:53:00.000------08:45:00---NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 11:09:00.000--2013-06-19 12:57:00.000--------NULL-----NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:06:00.000--2013-06-19 13:18:00.000--------NULL-----NULL
2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:45:00.000--2013-06-19 18:09:00.000--------NULL-----NULL
2013-01-05 00:00:00.000--17074-----G----2013-06-19 08:47:00.000--2013-06-19 18:01:00.000-------09:14:00---NULL
2013-01-06 00:00:00.000--17074-----O--------NULL-------------------------NULL --------------------------NULL----OFF DAY
2013-01-07 00:00:00.000--17074----NULL------------NULL-----------------NULL --------------------------NULL----CASUAL LEAVE



In Attend Log Table No Absent Data Inserted Only Employee Time IN and TIME OUT Inserted But I Join Attend_log Date into Leaveinformation Date In Query Which Is Gving Me Same Result.I DONT KNOW WHAT TO DO


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:44:58
sorry i'm not able to understand this.
As of now what i see here is you getting the value directly from LEAVEDESCRIPTION table based on the relationship set and its getting value (ABSENT) as stored in it. So didnt understand why you want it to be changed. If you want it to changed as per current logic, you should be changing the Lid value on leaveinformation table accordingly for eid,date combination.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -