Attendance Report Query!!! Need Help!!!
-
Hi All,
I have a view which has the below like data as output
http://www.nopaste.com/p/a1umSHdBx/txt
I wanted to represent this data like
Employee 1 01-01-2007 Absent
Employee 1 02-01-2007 Present
Employee 1 03-01-2007 Present
Which query is best for this?
If my question is not understandable, please reply..i shall try to put more info... my thought is attendance system is commonly known to everyone so may be this info is enough........
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
What have you tried, so far?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
That URL link just shows up as HTML (ie, I see the HTML code) to me rather than what I think you expect it to be which is a nicely formatted table.... Hmmm, actually in IE it works - Opera it doesn't... GO figure?
So what you want is for a range of dates to see, for each date, if an employee had a shift that day or not?
If this is it, then you'll want what's called a numbers table - Adam Machanic (might not be spelled correctly) has some good info on these. Lots of articles on this site.
You can treat the numbers table as a range of dates - each number is a date.
Then you can perform an outer join, or use an exists clause.
Perhaps
select EmpNames.EmpName, Dates.Date, case when exists(select * from Cards where [First Name]=EmpNames.EmpName and LogDate = Dates.Date) then 'Present' else 'Absent' end as DayStatus
from (select distinct [First Name] as EmpName from Cards) EmpNames
cross join Dates
would do what you want. The [Dates] table is yours to create from a numbers table. You could do something like
select EmpNames.EmpName, Dates.Date, case when exists(select * from Cards where [First Name]=EmpNames.EmpName and LogDate = Dates.Date) then 'Present' else 'Absent' end as DayStatus
from (select distinct [First Name] as EmpName from Cards) EmpNames
cross join (select dateAdd(d, numbers.number, '2007-01-01') as Date from numbers) dates
if you had a numbers table called numbers with a single column called number.
Hope that helps.
-
I created another table with 1,2,3,....31 as values for one column and i tried to put left outer join like the below
http://www.nopaste.com/p/a7EK0M81gb/txt
Here cms_date is the table which has a column with values from 1,2,3 ....31
vw_Attendance_Report is the view.
But what is happening is it tries to put outer join on whole dataset instead of each individual which is what i want....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
Hi Ian,
The same logic i had tried already but how can we know that employee was absent for somedays? The query will return only present days...This result i just wanted to publish in asp where i dont want to put any logic and all the logic i wanted to finish in SQL itself..
Jeff: Hope you got what i had tried...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
Try this:
....
FROM employee E
LEFT JOIN vw_Attendance_Report hj ON E.Empcode = hj.Empcode
RIGHT JOIN cms_date kj on kj.day_month =datepart(dd,FirstAccessDateTime)
order by E.empcode , kj.day_month
_____________
Code for TallyGenerator -
If i have only one employee record then below is the output of the query...
http://www.nopaste.com/p/aUDjixI6d/txt
But same employee table if i have 5 records then this is the output
http://www.nopaste.com/p/aMRaPHc3hb/txt
So finally if my employee table is having 2000 records and if atleast one employee came on any sunday then it will not show null for that sunday for anyother employee in the records...
Sergiy: Does this testing is fine??
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
First, the lecture...
If you want good quality help in a timely fashion, you must take the time to post your data in the form of a CREATE TABLE along with the necessary INSERT/SELECT UNION ALL statements 'cause folks just don't have the time or inclination to do what you should have
Ok... if you don't already have one, let's make a Tally (numbers) table that can handle a little over 30 years worth of dates, etc, etc.
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
And, because I'm lazy, here's what we're going to use to simulate your data...
--===== Create and populate test table called "yourtable".
SELECT TOP 500
RowNum = IDENTITY(INT,1,1),
Employee = CAST(RAND(CAST(NEWID() AS VARBINARY))*10+1 AS INT),
SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*90.0+36524.0 AS DATETIME)
INTO dbo.yourtable
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.yourtable
ADD PRIMARY KEY CLUSTERED (RowNum)
... and, finally, the solution to your original request. The key to this is that in order to figure out if an employee is absent on any particular date, you must have a reference that contains every date for every employee (derived table "de")... and you must do it carefully because it does require a CROSS JOIN and, if not done properly, can create millions of unwanted rows which will drag performance right into the toilet. Read the comments, study the code...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '20000201'
SET @EndDate = '20000301'
SELECT CONVERT(VARCHAR(10),de.TheDate,101) AS TheDate,
de.Employee,
CASE WHEN y.Employee IS NULL THEN 'Absent' ELSE 'Present' END AS Attendance
FROM (--==== Derived table finds unique date/employee combinations
SELECT DISTINCT
DATEADD(dd,DATEDIFF(dd,0,SomeDate),0) AS SomeDate,
Employee
FROM yourtable
)y
RIGHT OUTER JOIN
(--==== Derived table finds every date/employee combination
SELECT d.TheDate,e.Employee
FROM
(SELECT CAST(N-1 AS DATETIME)+@StartDate AS TheDate
FROM dbo.Tally
WHERE N<=DATEDIFF(dd,@StartDate,@EndDate)
)d
CROSS JOIN
(SELECT DISTINCT Employee
FROM yourtable
)e
)de
ON y.SomeDate = de.TheDate
AND y.Employee = de.Employee
ORDER BY de.TheDate,de.Employee
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff...I will getback to you after reading and understanding and testing this solution...Many Thanks!!
Regards,
Sakthi
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
You bet, Sakthi... thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hey Jeff,
What's so cool about your solutions, one can cut and paste them into QA and run them! And......they work! Great job.
Steve
-
Heh... I try to write "Fine" solutions because I've had "Mo-den-enough" with the ones that aren't
Thanks for the great compliment, Steve!--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply