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.
Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-19 : 06:45:10
|
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) tleft join leaveinformation l on t.eid = l.eid and t.date = l.dateleft join leavedescription u on l.lid = u.lidbut its not giving me the desired resultits giving me that resultdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT i want this type of resultdate-----------------------------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 thisdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave other shows like thisdate-----------------------------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 |
|
|
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 tablelike thiscid---bid-----eid---------date----------------------------------lid1-----1------17074-----2013-01-07 00:00:00.000--------1write 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 resultdate-----------------------------eid-----------timein------timeout-----spendtime-----remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL-----------1 its still show me this resultdate-----------------------------eid-----------timein------timeout-----spendtime-----remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL---------absent immad uddin ahmed |
|
|
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 thisdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave other shows like thisdate-----------------------------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 RemarksFROM LeaveInformation AINNER JOIN ATTEND_LOG B on A.eID=B.eID and A.bID=B.bIDWHERE TimeIn is NULL and TimeOut IS NULL CheersMIK |
|
|
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 LeaveDescFROM [ATTEND_LOG] aJOIN [LeaveInformation] li ON a.[BID] = li.[BID] AND a.EID = li.EIDJOIN [LeaveDescription] ld ON li.[LID] = ld.[LID][/code]--Chandu |
|
|
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))asbeginselect[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from attend_logwhere eid = @empidgroup by [date]selectE.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 ''endComments 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) ton t.[date] = t2.[date] and t.eid = t2.eidleft join employee e on e.eid = t.eidleft join designation d on e.designationid = d.designationidLEFT 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.DayLEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.ShiftLEFT OUTER JOIN Department T5 ON T5.did = e.didwhere t2.eid = @empidorder by t2.[date], t.[Timein]update ATTEND_LOG setexcessshort = 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 tleft join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Recordleft join employee e on e.eid = t.eidleft join designation d on e.designationid = d.designationidLEFT 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.DayLEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.ShiftLEFT OUTER JOIN Department T5 ON T5.did = e.didwhere t.eid=@empidendthis 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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-20 : 01:25:07
|
ALTER procedure [dbo].[p](@empid nvarchar(50))asbeginselect[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from attend_logwhere eid = @empidgroup by [date]selectt2.[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.DESCRIPTIONELSE '' END AS RemarksFROM (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) ton t.[date] = t2.[date] and t.eid = t2.eidleft join employee e on e.eid = t.eidleft outer join leaveinformation t9 on t.eid = t9.eid and t.date = t9.dateleft 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.DayLEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shiftwhere t2.eid = @empidorder by t2.[date], t.[Timein]endresult showing like thisdate---------------------------eid--------shift------timein--------------------------timeout---------------spendtime---remarks2013-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----NULL2013-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----NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 10:09:00.000--2013-06-19 12:23:00.000------NULL-------NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 12:32:00.000--2013-06-19 13:14:00.000-------NULL------NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 13:30:00.000--2013-06-19 18:10:00.000-------NULL------NULL2013-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---NULL2013-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---NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 11:09:00.000--2013-06-19 12:57:00.000--------NULL-----NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:06:00.000--2013-06-19 13:18:00.000--------NULL-----NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:45:00.000--2013-06-19 18:09:00.000--------NULL-----NULL2013-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---NULL2013-01-06 00:00:00.000--17074-----O--------NULL-------------------------NULL --------------------------NULL----OFF DAY2013-01-07 00:00:00.000--17074----NULL------------NULL-----------------NULL --------------------------NULL----ABSENTI WANT THIS TYPE OF RESULT if data inserted in leave information tableother wise show absentdate---------------------------eid--------shift------timein--------------------------timeout---------------spendtime---remarks2013-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----NULL2013-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----NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 10:09:00.000--2013-06-19 12:23:00.000------NULL-------NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 12:32:00.000--2013-06-19 13:14:00.000-------NULL------NULL2013-01-02 00:00:00.000--17074-----G----2013-06-19 13:30:00.000--2013-06-19 18:10:00.000-------NULL------NULL2013-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---NULL2013-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---NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 11:09:00.000--2013-06-19 12:57:00.000--------NULL-----NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:06:00.000--2013-06-19 13:18:00.000--------NULL-----NULL2013-01-04 00:00:00.000--17074-----G----2013-06-19 13:45:00.000--2013-06-19 18:09:00.000--------NULL-----NULL2013-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---NULL2013-01-06 00:00:00.000--17074-----O--------NULL-------------------------NULL --------------------------NULL----OFF DAY2013-01-07 00:00:00.000--17074----NULL------------NULL-----------------NULL --------------------------NULL----CASUAL LEAVEIn 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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|