SELECT distinct case when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end eid,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS Absent,CASE WHEN al.eid is not null and w.ename is not null and w.leavingdate is null and al.status='I' and NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL then 'P' else '' end PresentsFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0--left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.date--left join LeaveDescription ld on ld.lid =l.lidWHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916

SELECT distinct case when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end eid,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS Absent,CASE WHEN al.eid is not null and w.ename is not null and w.leavingdate is null and al.status='I' and NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL then 'P' else '' end PresentsFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0--left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.date--left join LeaveDescription ld on ld.lid =l.lidWHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916

Thanks

immad uddin ahmed

I might be misreading this, but the query you are currently using seems to be not in line with the output that you are trying to get. For example, in the output you have columns as Present, Absent etc. whcih are not in the output.

Regardless, if your objective is to eliminate rows where there is no 'P' in the attendance column, add another clause to your left join like this: "AND attendance <> 'P'"

select * from (SELECT distinctcase when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end eid,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS Absent,CASE WHEN al.eid is not null and w.ename is not null and w.leavingdate is null and al.status='I' and NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL then 'P' else '' end PresentsFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0--left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.date--left join LeaveDescription ld on ld.lid =l.lidWHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916)a where a.Present='P'

add this condition to the WHERE clauseAND al.eID = case when w.leavingdate is null and al.status='I' then al.eID else '' end

--Chandu

only P data is showing. i write this query

SELECT distinct case when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end eid,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS AbsentFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 AND al.eID = case when w.leavingdate is null and al.status='I' then al.eID else '' end

What is the output of the following query...SELECT * FROM (SELECT distinctcase when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end EID,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS AbsentFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) TWHERE T.EID IS NOT NULL;

What is the output of the following query...SELECT * FROM (SELECT distinctcase when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date,case when w.leavingdate is null and al.status='I' then al.eID else '' end EID,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS AbsentFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) TWHERE T.EID IS NOT NULL;