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

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/19/2013 :  09:01:15  Show Profile  Reply with Quote


DAte------------------------eid------attendance
2013-07-01 09:09:00.000-----16916----P
2013-07-01 18:00:00.000--------------
2013-07-02 08:54:00.000------16916---P
2013-07-02 18:00:00.000---------------
2013-07-03 08:56:00.000------16916---P
2013-07-03 18:00:00.000---------------
2013-07-04 08:53:00.000------16916---P
2013-07-04 19:45:00.000---------------

i want this type of data
date------------------------eid------attendance
2013-07-01 09:09:00.000-----16916-------P
2013-07-02 08:54:00.000------16916------P
2013-07-03 08:56:00.000------16916------P
2013-07-04 08:53:00.000------16916------P


if there is no p show in attendance then date will also not show


i am using 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 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 Presents
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 ld.lid =l.lid
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916


Thanks

immad uddin ahmed

Edited by - immad on 08/19/2013 09:01:51

James K
Flowing Fount of Yak Knowledge

3717 Posts

Posted - 08/19/2013 :  11:13:11  Show Profile  Reply with Quote
quote:
Originally posted by immad



DAte------------------------eid------attendance
2013-07-01 09:09:00.000-----16916----P
2013-07-01 18:00:00.000--------------
2013-07-02 08:54:00.000------16916---P
2013-07-02 18:00:00.000---------------
2013-07-03 08:56:00.000------16916---P
2013-07-03 18:00:00.000---------------
2013-07-04 08:53:00.000------16916---P
2013-07-04 19:45:00.000---------------

i want this type of data
date------------------------eid------attendance
2013-07-01 09:09:00.000-----16916-------P
2013-07-02 08:54:00.000------16916------P
2013-07-03 08:56:00.000------16916------P
2013-07-04 08:53:00.000------16916------P


if there is no p show in attendance then date will also not show


i am using 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 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 Presents
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 ld.lid =l.lid
WHERE 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'"
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
666 Posts

Posted - 08/19/2013 :  11:16:33  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
can you try this,if it works.

select * from (
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 Presents
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 ld.lid =l.lid
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916
)a where a.Present='P'

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/20/2013 :  02:02:11  Show Profile  Reply with Quote
actullay i forget something which i want it to show u

my actual data is like this


date--------------------------EID---------Present------Absent
2013-07-01 09:09:00.000---16916--------P------------
2013-07-01 18:00:00.000-----------------------------
2013-07-02 08:54:00.000---16916--------P------------
2013-07-02 18:00:00.000---------------------------
2013-07-03 08:56:00.000---16916--------P------------
2013-07-03 18:00:00.000-----------------------------
2013-07-04 00:00:00.000---16916---------------------A
2013-07-05 08:51:00.000---16916--------P------------
2013-07-05 18:00:00.000-----------------------------

this is my 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 Absent
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
WHERE c.[Date] >= '20130701' and c.date<= '20130705' and w.eid=16916

i want this type of data

date---------------------------EID------Present------Absent
2013-07-01 09:09:00.000---16916--------P------------
2013-07-02 08:54:00.000---16916--------P------------
2013-07-03 08:56:00.000---16916--------P------------
2013-07-04 00:00:00.000---16916---------------------A
2013-07-05 08:51:00.000---16916--------P------------

if there is no P and NO A then no Date and no EID show

Thanks For the help


immad uddin ahmed

Edited by - immad on 08/20/2013 02:02:46
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/20/2013 :  02:17:12  Show Profile  Reply with Quote
add this condition to the WHERE clause
AND al.eID = case when w.leavingdate is null and al.status='I' then al.eID else '' end

--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/20/2013 :  02:30:19  Show Profile  Reply with Quote
quote:
Originally posted by bandi

add this condition to the WHERE clause
AND 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 Absent
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
WHERE 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


and its showing this data

Date---------------------eid-------present------Absent
2013-07-01 09:09:00.000--16916---------P---------
2013-07-02 08:54:00.000--16916---------P---------
2013-07-03 08:56:00.000--16916---------P---------
2013-07-04 08:53:00.000--16916---------P---------
2013-07-05 09:06:00.000--16916---------P---------
2013-07-05 13:58:00.000--16916---------P---------
2013-07-06 08:59:00.000--16916---------P---------
2013-07-08 08:51:00.000--16916---------P---------

its not showing 7th date but when i remove this syntax its show present and absent data and without absnet and present data



immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/20/2013 :  02:37:44  Show Profile  Reply with Quote
--may be this?
AND (case when w.leavingdate is null and al.status='I' then al.eID end) IS NOT NULL

--Alternate is
SELECT *
FROM (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
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
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) T
WHERE T.eid != ''


--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/20/2013 :  02:40:20  Show Profile  Reply with Quote
may be its very complex.becouse none of the solution is working.still same result

immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/20/2013 :  03:03:54  Show Profile  Reply with Quote
What is the output of the following query...
SELECT *
FROM (
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
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
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) T
WHERE T.EID IS NOT NULL;



--
Chandu

Edited by - bandi on 08/20/2013 03:06:49
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/20/2013 :  03:18:09  Show Profile  Reply with Quote
quote:
Originally posted by bandi

What is the output of the following query...
SELECT *
FROM (
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
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
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) T
WHERE T.EID IS NOT NULL;



--
Chandu



The out put of query is

DAte--------------------------EID----------Present---Absent
2013-07-01 09:09:00.000--16916-------P------------
2013-07-01 18:00:00.000
2013-07-02 08:54:00.000--16916-------P-----------
2013-07-02 18:00:00.000
2013-07-03 08:56:00.000--16916-------P-----------
2013-07-03 18:00:00.000
2013-07-04 08:53:00.000--16916-------P-----------
2013-07-04 19:45:00.000
2013-07-05 09:06:00.000--16916-------P-----------
2013-07-05 13:00:00.000
2013-07-05 13:58:00.000--16916-------P
2013-07-05 18:00:00.000
2013-07-06 08:59:00.000--16916-------P
2013-07-06 18:00:00.000
2013-07-07 00:00:00.000------------------------------A



i dont want data that written in red becouse there is no A and no P
immad uddin ahmed

Edited by - immad on 08/20/2013 03:22:44
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/20/2013 :  03:51:59  Show Profile  Reply with Quote
--try this.. highlighted part is the modification
SELECT * FROM (
....
 ) T
WHERE (NULLIF(T.EID,'') IS NOT NULL 
 OR NULLIF(T.Present,'') IS NOT NULL
 OR NULLIF(T.Absent,'') IS NOT NULL);



--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 08/20/2013 :  04:22:50  Show Profile  Reply with Quote
Thanks :)
i learn lots of thinks in this query i dont know that we can use two where in a query

immad uddin ahmed

Edited by - immad on 08/20/2013 04:29:36
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/20/2013 :  04:32:21  Show Profile  Reply with Quote
quote:
Originally posted by immad

Thanks :)
i learn lots of thinks in this query i dont know that we can use two where in a query

immad uddin ahmed


any way worked...right?

Welcome

--
Chandu
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.11 seconds. Powered By: Snitz Forums 2000