Query - SQL Server Forums

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

Query

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-08-15 : 04:43:49

Hello

i have a query problem

this is my query

select
distinct a.eid,
e.ename,
CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101)) Date,
case when a.status='I' and a.status= 'O' then '' else 'P' end Attendance,
from attendlog a
left outer join employee e on a.eid = e.eid
where CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))>'20130801' and CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))<'20130815'
order by date

its gives me that result

eid--------name---------------date--------------attendance
1-----------A--------2013-08-02 00:00:00.000-------P
10----------B--------2013-08-02 00:00:00.000-------P
1015--------C--------2013-08-02 00:00:00.000-------P

but if some employee absent on that date its didnt show absent 'A'
becouse attendlog table didnt have absent employee data
please help me out

immad uddin ahmed

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-08-15 : 06:24:50

case when a.status='I' and a.status= 'O' then '' else 'P' end Attendance

This line will always return 'P'.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-15 : 06:45:36

[code]SELECT e.eID,
e.eName,
d.theDate AS [Date],
CASE
WHEN al.[Status] IS NULL THEN 'A'
WHEN al.[Status] IN ('I', 'O') THEN ''
ELSE 'P'
END Attendance,
FROM (
SELECT CAST(CheckTime AS DATE) AS theDate
FROM dbo.AttendLog
WHERE CheckTime >= '20130801'
AND CheckTime < '20130815'
GROUP BY CAST(CheckTime AS DATE)
) AS d
CROSS JOIN dbo.Employee AS e
LEFT JOIN dbo.AttendLog AS al ON al.eID = e.eID
AND CAST(al.CheckTime AS DATE) = d.theDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-08-15 : 08:47:34

actually there is no absent data in attend log i mean only that employee attendance data insert whose present in factory if a employee is absent then there is no data in attendlog so how i can retrive that data

for example this is a attend log data

eid-------checktime------------------status
25391----2013-01-01 06:51:00.000-------I
25391----2013-01-02 09:41:00.000-------I
25391----2013-01-03 08:20:00.000-------I
25391----2013-01-05 07:20:00.000-------I

this employee didnt come on 4 jan becouse he is absent so how its possible to retrive his absent data.

immad uddin ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources