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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Data Doubling

Author  Topic 

immad
Posting Yak Master

230 Posts

Posted - 2013-08-19 : 09:01:15


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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-19 : 11:13:11
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

737 Posts

Posted - 2013-08-19 : 11:16:33
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

230 Posts

Posted - 2013-08-20 : 02:02:11
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 02:17:12
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

230 Posts

Posted - 2013-08-20 : 02:30:19
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 02:37:44
--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

230 Posts

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

immad uddin ahmed
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 03:03:54
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
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-08-20 : 03:18:09
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 03:51:59
--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

230 Posts

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

immad uddin ahmed
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 04:32:21
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
   

- Advertisement -