Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Absent Problem Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 11:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 6:51 AM
Points: 99, Visits: 301
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
Post #1484983
Posted Friday, August 16, 2013 4:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Post #1485091
Posted Friday, August 16, 2013 5:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 6:51 AM
Points: 99, Visits: 301
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
Post #1485111
Posted Friday, August 16, 2013 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Post #1485120
Posted Friday, August 16, 2013 5:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 6:51 AM
Points: 99, Visits: 301
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 (
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 :)

Post #1485130
Posted Friday, August 16, 2013 5:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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


“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
Post #1485134
Posted Friday, August 16, 2013 6:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 6:51 AM
Points: 99, Visits: 301
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
Post #1485143
Posted Friday, August 16, 2013 6:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Post #1485164
Posted Saturday, August 17, 2013 12:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 6:51 AM
Points: 99, Visits: 301
Thanks
Post #1485440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse