SQL query -- is this possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

New to SQL Server Programming

SQL query -- is this possible?

Author

Topic

mkowske
Starting Member

4 Posts

Posted - 2011-02-03 : 12:57:36

So here are the conditions of the query I'm trying to construct. There is an "attendance" table that holds attendance records for employees (vacation, sick day, tardy, etc). Relevant columns are

emp_id (employee id, integer)
attend_code (type of record, string value)
attend_date (date of record)

Given a particular date I would like to get results that have a attend_code of DO for that day, but ONLY if that particular employee has had 3 previous DO records for the current quarter (Jan 1st to Mar 31st). Is this possible with one query?

select * from attendance where attend_date=? and attend_code="DO" and (select count(*) from attendance as a join attendance on a.emp_id=attendance.emp_id where attend_date>"01/01/2011" and attend_date<"03/31/2011" and attend_code="DO") > 3

So the above obviously doesn't work... but it was my first inclination. Maybe possible with GROUP BY or table views or something? Thanks for any help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-03 : 13:14:48

SELECT EmpID
FROM dbo.Table1
WHERE Attend_Code = 'DO'
GROUP BY EmpID
HAVING MAX(CASE WHEN Attend_Date = @WantedDate THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN Attend_Date >= DATEADD(QUARTER, DATEADD(Quarter, 0, @WantedDate), 0) AND Attend_Date < @WantedDate THEN 1 ELSE 0 END) >= 3

N 56°04'39.26"
E 12°55'05.63"

mkowske
Starting Member

4 Posts

Posted - 2011-02-03 : 15:57:12

Very cool.. thank you. What if this particular implementation of SQL doesn't support the CASE statement?

mkowske
Starting Member

4 Posts

Posted - 2011-02-03 : 16:12:13

Disregard. I think I figured it out with equivalent if statements:

SELECT EmpID
FROM dbo.Table1
WHERE Attend_Code = 'DO'
GROUP BY EmpID
HAVING MAX( IF(Attend_Date = @WantedDate, 1, 0) = 1
AND SUM( IF( (Attend_Date >= DATEADD(QUARTER, DATEADD(Quarter, 0, @WantedDate), 0) AND Attend_Date < @WantedDate), 1, 0) ) >= 3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-04 : 05:10:43

quote:
Originally posted by mkowske

Disregard. I think I figured it out with equivalent if statements:

SELECT EmpID
FROM dbo.Table1
WHERE Attend_Code = 'DO'
GROUP BY EmpID
HAVING MAX( IF(Attend_Date = @WantedDate, 1, 0) = 1
AND SUM( IF( (Attend_Date >= DATEADD(QUARTER, DATEADD(Quarter, 0, @WantedDate), 0) AND Attend_Date < @WantedDate), 1, 0) ) >= 3

It means that you are not using MS SQL Server

Madhivanan

Failing to plan is Planning to fail

mkowske
Starting Member

4 Posts

Posted - 2011-02-04 : 12:49:04

There's been a few conditionals added to this since I first posted. Same thing except now also need to search for over 3 occurances of "T" as well as "DO" and include in the results any record that occurs on specified date with code "TX" or "NS". I thought the following would do it... the results seem mostly good, but a little unpredictable. Do I have something wrong? This example for a specified date of 03/12/2010:

SELECT id,emp_id,attend_date,attend_code FROM emp_attendance WHERE
attend_date >= "01/01/2010" AND attend_date <= "03/31/2010" AND
(attend_code="NS" OR attend_code="TX" OR attend_code="DO" OR attend_code="T")
GROUP BY emp_id
HAVING
MAX( IF(attend_date="03/12/2010", 1, 0) ) = 1
AND
(
(attend_code="NS" OR attend_code="TX")
OR
( SUM( IF( attend_code="T", 1, 0 ) ) > 3 )
OR
( SUM( IF( attend_code="DO", 1, 0 ) ) > 3 )
);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources