select query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

select query

Author

Topic

san79
Starting Member

42 Posts

Posted - 2008-04-22 : 02:43:26

hi i have a table containing three fields
empid, ddate and status
the status has three values P(present),A(Absent), and NA
i am trying to retrieve data for those who are continuously absent between dates which i will give as input, i can do it in hard coded way but i wish to know that is there some query to achieve this.

regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 03:44:02

SELECT EmpID
FROM Table
WHERE dDate >= '20080101'
AND dDate < '20080201'
GROUP BY EmpID
HAVING MIN(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) = 1

E 12°55'05.25"
N 56°04'39.16"

san79
Starting Member

42 Posts

Posted - 2008-04-22 : 10:05:26

hello mr.peso
Thanks for the query, it works really good, now if i add ddate in the selection criteria the result is bizarre i.e. it also take the other employee who are absent for a day or two in the given criteria. my query is
SELECT empid,ddate
FROM dbo.tbltest
WHERE (ddate >= '20070401') AND (ddate < '20070405')
GROUP BY empid,ddate
HAVING (MIN(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) = 1)

the out put is

1 20070401
2 20070401
1 20070402
2 20070402
1 20070403
1 20070404
2 20070404
but i want my output only represent those employees who are continuously absent like this
1 20070401
1 20070402
1 20070403
1 20070404
1 20070405

how to obtain the result, sorry if it is silly, i am just started

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:18:29

To start with, you didn't provide any sample data to work with.
Please read and understand this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
and follow the advices given.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:23:05

[code]-- Mimic user supplied parameters to stored procedure or function
DECLARE @FirstDate DATETIME,
@LastDate DATETIME

SELECT @FirstDate = '20070401',
@LastDate = '20070405'

-- Here is the code needed to accomplish your requirements
SELECT @FirstDate = DATEDIFF(DAY, '19000101', @FirstDate),
@LastDate = DATEDIFF(DAY, '18991231', @LastDate)

SELECT EmpID
FROM tblTest
WHERE dDate >= @FirstDate
AND dDate < @LastDate
AND Status = 'A'
GROUP BY EmpID
HAVING COUNT(DISTINCT DATEDIFF(DAY, '19000101', dDate)) = DATEDIFF(DAY, @FirstDate, @LastDate)[/code]

E 12°55'05.25"
N 56°04'39.16"

san79
Starting Member

42 Posts

Posted - 2008-04-22 : 10:26:19

understood will do the needful
Thanks for the pointer :-)

san79
Starting Member

42 Posts

Posted - 2008-04-22 : 10:28:46

to the second posting
will try and get back to you sir,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:31:48

If all dDate do not have time information other than '00:00:00', you can use this

-- Mimic user supplied parameters to stored procedure or function
DECLARE	@FirstDate DATETIME,
	@LastDate DATETIME

SELECT	@FirstDate = '20070401',
	@LastDate = '20070404'

-- Here is the code needed to accomplish your requirements
SELECT		EmpID
FROM		tblTest
WHERE		dDate BETWEEN @FirstDate AND LastDate
		AND Status = 'A'
GROUP BY	EmpID
HAVING		COUNT(DISTINCT dDate) = DATEDIFF(DAY, @FirstDate, @LastDate) + 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources