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
 LEAVE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/19/2013 :  06:45:10  Show Profile  Reply with Quote

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

Edited by - immad on 06/19/2013 06:46:07

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/19/2013 :  06:54:15  Show Profile  Reply with Quote
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

Pakistan
219 Posts

Posted - 06/19/2013 :  07:04:11  Show Profile  Reply with Quote
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

Edited by - immad on 06/19/2013 07:09:47
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/19/2013 :  07:50:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/19/2013 :  08:03:32  Show Profile  Reply with Quote
--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]


--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/19/2013 :  08:22:24  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/19/2013 :  10:31:56  Show Profile  Reply with Quote
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

Pakistan
219 Posts

Posted - 06/20/2013 :  01:25:07  Show Profile  Reply with Quote


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

India
52317 Posts

Posted - 06/20/2013 :  01:44:58  Show Profile  Reply with Quote
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
  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.09 seconds. Powered By: Snitz Forums 2000