SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Absent Problem


Absent Problem

Author
Message
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 420
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-01 00:00:00.000----------P
1-------------A--------2013-08-02 00:00:00.000----------P
1-------------A--------2013-08-04 00:00:00.000----------P

but if A employee absent on 3 august then that data of absent not in attendlog
becouse attendlog table didnt have absent employee data
please help me out i want to find out absent data of employee

CREATE DATABASE dbname;


CREATE TABLE Attendlog
(
EID int,
Name varchar(10),
date datetime,
Attendnace varchar(10)
);

INSERT INTO attendlog (eid,name,date,attendance)
VALUES (1,A,2013-08-01 00:00:00.000,P);


INSERT INTO attendlog (eid,name,date,attendance)
VALUES (1,A,2013-08-02 00:00:00.000,P);


INSERT INTO attendlog (eid,name,date,attendance)
VALUES (1,A,2013-08-04 00:00:00.000,P);

Thanks
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17040 Visits: 19557
immaduddinahmed (8/15/2013)

from attendlog a
left outer join employee e on a.eid = e.eid
If an employee has no attendance record for the selected time period then they will be excluded from the output. Either reverse the join (from employee left outer join attendlog) or, better still, use a full outer join.


where CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))>'20130801' and CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))<'20130815'
If you convert your constants to match the datatype of your columns, then SQL Server won’t have to do a full table scan of 10 years to pick up a single day’s worth of data.

You could process the attendlog table to provide a set of distinct dates and left join a set containing a product of both tables to it. You could also use a calendar table in the same way, restricting dates in the same way as you are at present with the attendlog table. Either way, you will have the “missing dates”. Something like

SELECT *
FROM Calendar c
LEFT JOIN (
SELECT
eid = COALESCE(a.eid,e.eid),
a. CheckTIme
FROM employee e
FULL OUTER JOIN attendlog a
ON a.eid = e.eid
) a
ON a. CheckTIme = c.Date
WHERE c.Date > CAST('20130801' AS DATETIME) AND c.Date < CAST(‘20130815’ AS DATETIME)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 420
SELECT
c.date,
a.eid,
a.checktime
FROM Calendar c
LEFT JOIN (
SELECT
eid = COALESCE(a.eid,e.eid),
a. CheckTIme
FROM employee e
FULL OUTER JOIN attendlog a
ON a.eid = e.eid
) a
ON a. CheckTIme = c.Date
WHERE c.Date > CAST('20130731' AS DATETIME) AND c.Date < CAST('20130815' AS DATETIME)

well i use this query but its giving me this result

date-----------eid-------------checktime
2013-08-01----NULL-------------NULL
2013-08-02----NULL-------------NULL
2013-08-03----NULL-------------NULL
2013-08-04----NULL-------------NULL
2013-08-05----NULL-------------NULL

Thanks for the help
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17040 Visits: 19557
My mistake, coding before thinking. Try this:

SELECT *
FROM Calendar c
CROSS JOIN employee e
LEFT JOIN attendlog a
ON a.eid = e.eid
AND a.CheckTIme = c.Date
WHERE c.[Date] >= CAST('20130801' AS DATETIME)
AND c.[Date] <= CAST('20130815' AS DATETIME)
ORDER BY e.eid



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 420
ChrisM@Work (8/16/2013)
My mistake, coding before thinking. Try this:

SELECT *
FROM Calendar c
CROSS JOIN employee e
LEFT JOIN attendlog a
ON a.eid = e.eid
AND a.CheckTIme = c.Date
WHERE c.[Date] >= CAST('20130801' AS DATETIME)
AND c.[Date] <= CAST('20130815' AS DATETIME)
ORDER BY e.eid



thanks but i pass that level Smile now i am getting problem in another level

this is the query

SELECT
distinct w.eID,
w.status,
COALESCE(al.CheckTime, c.[Date]) AS [Date]

FROM (
SELECT eID,status
FROM dbo.AttendLog
GROUP BY eID,status
) 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 date='20130731'

after execute query data show like this
EID--------status---------Date
17028--------O-----2013-07-31 08:11:00.000
204----------O-----2013-07-31 15:45:00.000
25416--------O-----2013-07-31 16:02:00.000
2634---------I-----2013-07-31 00:00:00.000
26318--------O-----2013-07-31 00:00:00.000
1312---------O-----2013-07-31 00:00:00.000


how i show result like this

EID--------status---------Date---------------Attendance
17028--------O-----2013-07-31 08:11:00.000-----P
204----------O-----2013-07-31 15:45:00.000-----P
25416--------O-----2013-07-31 16:02:00.000-----P
2634---------I-----2013-07-31 00:00:00.000-----A
26318--------O-----2013-07-31 00:00:00.000-----A
1312---------O-----2013-07-31 00:00:00.000-----A

means if date its not showing time then attendance is A if date show
time the attendance P

actulay this work is above my level thats why i am getting complexity Smile
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17040 Visits: 19557
immaduddinahmed (8/16/2013)
...
actulay this work is above my level thats why i am getting complexity Smile



You're doing just fine - you've learned a huge amount in the time you've been posting on ssc.

Try this in your output list:

CASE WHEN al.CheckTime IS NULL THEN 'A' ELSE 'P' END

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 420
i write this is the query

SELECT
distinct w.eID,
w.status,
COALESCE(al.CheckTime, c.[Date]) AS [Date]
,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE 'P' END AS Attendance
FROM (
SELECT eID,status
FROM dbo.AttendLog
GROUP BY eID,status
) 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 date='20130731'


but i am getting problem in employee name

how i write employee e.name in this query
when i write my ename in query my absnet data dont show
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17040 Visits: 19557
I think it should look more like this:
SELECT  
w.eID, w.name,
al.[status],
COALESCE(al.CheckTime, c.[Date]) AS [Date]
,CASE WHEN al.CheckTime IS NULL THEN 'A' ELSE 'P' END AS Attendance
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] = '20130731'



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 420
Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search