Query Showing Employess Absent

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Showing Employess Absent

Author

Topic

masteripper
Starting Member

25 Posts

Posted - 2009-06-18 : 01:48:21

Hi to everybody.
I has this problem with a report i need and i would really like some help.
I have a table with the workers leaves.
The table is like this
EmployeeID LeaveStartDate LeaveEndDate
What is i need is some what a dynamic calendar of absence
Lets say
WorkerA gets a leave at 20/06/2009 which ends at 22/06/2009
WorkerB gets a leave at 18/06/2009 which ends at 21/06/2009
The calendar/Report would go something like this

22/06/2009
--------------------------
WorkerA Absent
................................
21/06/2009
--------------------------
WorkerA Absent
WorkerB Absent
.................................
20/06/2009
------------------------
WorkerA Absent
WorkerB Absent
..................................
19/06/2009
-------------------------
WorkerB Absent
...............................
18/06/2009
-------------------------
WorkerB Absent
................................

Well can this being done with a single query or i have programmaticaly add the Pseudo Records
I need an output from the query like this

EmployeID DateAbsent

Thanks in advance

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-18 : 02:33:22

Ideal situation where a Calendar table can help you.
See - http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Create a Calender Table and just join it with your query get all the dates between the leave start date and end date.

also see -
http://mangalpardeshi.blogspot.com/2008/12/how-to-create-time-dimension-in-sql.html

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

masteripper
Starting Member

25 Posts

Posted - 2009-06-18 : 03:28:53

It seems like a lot of reading....
Well maybe i should go the programming way.Retrieve the data and manipulate them in order to get the output i need.
Thanks anyway.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-18 : 03:36:40

Well its not that much of reading.
Put it in a simple way -
Create a Calendar Table, Left Join it with workers leaves table. So you will get all the dates.

And this approch will give you faster result, compare to craeting missing dates by programming

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

masteripper
Starting Member

25 Posts

Posted - 2009-06-18 : 07:26:28

Well to let you know i just made it .... by programming
Thanks for your interest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources