Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LEaves Expand / Collapse
Author
Message
Posted Saturday, August 17, 2013 12:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:48 AM
Points: 105, Visits: 324
Hi.

i am using this query.


SELECT
w.eID, w.ename,
COALESCE(al.CheckTime, c.[Date]) AS [Date]
,CASE WHEN al.CheckTime IS NULL THEN ld.Description ELSE 'P' END AS Attendance
FROM dbo.employee AS w
CROSS JOIN dbo.Calendar AS c
LEFT JOIN dbo.AttendLog AS al
ON al.eID = w.eID
AND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0
left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.date
left join LeaveDescription ld on l.lid =ld.lid

WHERE c.[Date] = '20130815'

when i write ld.Description its gives me null data like this

eid----------name--------------date----------------attendance
101881--------A----2013-08-15 00:00:00.000-----------NULL
101798--------B----2013-08-15 00:00:00.000-----------NULL
101775--------C----2013-08-15 00:00:00.000-----------NULL

but when i use 'A' instead of ld.Description its gives me that result

eid----------name--------------date----------------attendance
101881--------A----2013-08-15 00:00:00.000-----------A
101798--------B----2013-08-15 00:00:00.000-----------A
101775--------C----2013-08-15 00:00:00.000-----------A


i want to write ld.descriprtion instead of A
Please help me out Thanks
Post #1485442
Posted Tuesday, August 20, 2013 10:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:58 PM
Points: 522, Visits: 241
Can you check in table if ld.Description has some values other than NULL? Query looks fine otherwise.
Post #1486363
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse