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
 General SQL Server Forums
 Script Library
 Need Query Help

Author  Topic 

niralas
Starting Member

2 Posts

Posted - 2013-02-19 : 10:46:28
I have to write a query to count attendance for office location and print office is close or Open

having 3 tables

Employee, Office_Location, Emp_Attendance

Employee (Emp_ID, EMP_Name, Office_ID)

Office_Location (Office_ID, Office_Address)

Emp_Attendance (Date, Emp_ID, Status, Remarks)


Now I have to say Office is close if no employee present on certain date

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 12:13:26
If you don't already have a calendar table in your database, create one with the dates of interest like this:
Create a calendar table first, if you don't have one already like this:

CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);
;WITH cte AS
(
SELECT CAST('20130101' AS DATETIME) AS c
UNION ALL
SELECT DATEADD(dd,1,c) FROM cte
WHERE c < '20131231'
)
INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);
Then query against that table. For example:
SELECT
c.Dt,
o.Office_ID
FROM
#Calendar c
CROSS JOIN Office_Location o
WHERE NOT EXISTS
(
SELECT
*
FROM
Emp_Attendance ea
INNER JOIN Employee e ON e.Emp_ID = ea.EmpID
WHERE
o.Office_ID = e.Office_ID
AND ea.Date = c.Dt
);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 01:21:01
duplicate of

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183103

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -