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-08-19 : 09:01:15
|
DAte------------------------eid------attendance2013-07-01 09:09:00.000-----16916----P 2013-07-01 18:00:00.000-------------- 2013-07-02 08:54:00.000------16916---P2013-07-02 18:00:00.000--------------- 2013-07-03 08:56:00.000------16916---P2013-07-03 18:00:00.000--------------- 2013-07-04 08:53:00.000------16916---P2013-07-04 19:45:00.000--------------- i want this type of datadate------------------------eid------attendance2013-07-01 09:09:00.000-----16916-------P 2013-07-02 08:54:00.000------16916------P2013-07-03 08:56:00.000------16916------P2013-07-04 08:53:00.000------16916------Pif 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 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=16916Thanksimmad 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------attendance2013-07-01 09:09:00.000-----16916----P 2013-07-01 18:00:00.000-------------- 2013-07-02 08:54:00.000------16916---P2013-07-02 18:00:00.000--------------- 2013-07-03 08:56:00.000------16916---P2013-07-03 18:00:00.000--------------- 2013-07-04 08:53:00.000------16916---P2013-07-04 19:45:00.000--------------- i want this type of datadate------------------------eid------attendance2013-07-01 09:09:00.000-----16916-------P 2013-07-02 08:54:00.000------16916------P2013-07-03 08:56:00.000------16916------P2013-07-04 08:53:00.000------16916------Pif 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 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=16916Thanksimmad 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'" |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-08-19 : 11:16:33
|
can you try this,if it works.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'mohammad.javeed.ahmed@gmail.com |
|
|
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 thisdate--------------------------EID---------Present------Absent2013-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---------------------A2013-07-05 08:51:00.000---16916--------P------------2013-07-05 18:00:00.000----------------------------- this is my querySELECT 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<= '20130705' and w.eid=16916 i want this type of datadate---------------------------EID------Present------Absent2013-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---------------------A2013-07-05 08:51:00.000---16916--------P------------if there is no P and NO A then no Date and no EID showThanks For the help immad uddin ahmed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-20 : 02:17:12
|
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 |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-08-20 : 02:30:19
|
quote: Originally posted by bandi 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 querySELECT 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 and its showing this dataDate---------------------eid-------present------Absent2013-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 dataimmad uddin ahmed |
|
|
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 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 != ''--Chandu |
|
|
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 resultimmad uddin ahmed |
|
|
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 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;--Chandu |
|
|
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 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;--Chandu
The out put of query isDAte--------------------------EID----------Present---Absent2013-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------------------------------Ai dont want data that written in red becouse there is no A and no Pimmad uddin ahmed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-20 : 03:51:59
|
--try this.. highlighted part is the modificationSELECT * FROM (.... ) TWHERE (NULLIF(T.EID,'') IS NOT NULL OR NULLIF(T.Present,'') IS NOT NULL OR NULLIF(T.Absent,'') IS NOT NULL); --Chandu |
|
|
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 queryimmad uddin ahmed |
|
|
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 queryimmad uddin ahmed
any way worked...right?Welcome--Chandu |
|
|
|
|
|
|
|