August 15, 2013 at 11:27 pm
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
immad
August 16, 2013 at 4:39 am
immaduddinahmed (8/15/2013)
from attendlog aleft 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)
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
August 16, 2013 at 5:15 am
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
immad
August 16, 2013 at 5:34 am
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
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
August 16, 2013 at 5:47 am
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 🙂 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(
SELECTeID,status
FROMdbo.AttendLog
GROUP BYeID,status
) AS w
CROSS JOINdbo.Calendar AS c
LEFT JOINdbo.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 🙂
immad
August 16, 2013 at 5:57 am
immaduddinahmed (8/16/2013)
...actulay this work is above my level thats why i am getting complexity 🙂
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
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
August 16, 2013 at 6:13 am
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(
SELECTeID,status
FROMdbo.AttendLog
GROUP BYeID,status
) AS w
CROSS JOINdbo.Calendar AS c
LEFT JOINdbo.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
immad
August 16, 2013 at 6:51 am
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'
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
August 17, 2013 at 12:23 am
Thanks
immad
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply