asked on

t-sql Get People who Did not do something between Specified Dates

Hi:

I have 2 tables -
Persons with fields PersonId and PersonName and
Documents with DocumentId, PersonId and DocumentDate

I would like to create a stored procedure that gets a list of all Persons who do not have a Document with DocumentDate between 2 specified parameter dates.

Any help creating this would be greatly appreciated.

roger

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Something like this perhaps:

CREATE PROCEDURE usp_GetUndocumentedPersons
			@FromDate datetime,
			@ToDate datetime
AS

SET NOCOUNT ON

SELECT	p.*
FROM	Persons p
	LEFT JOIN (
		SELECT	PersonId
		FROM	Documents 
		WHERE	DocumentDate BETWEEN @FromDate AND @ToDate
		GROUP BY
			PersonId) d ON p.PersonId = d.PersonId
WHERE	d.PersonID IS NULL

Open in new window

Anthony Perkins

If your DocumentDate has time than you will have to do something like this:

CREATE PROCEDURE usp_GetUndocumentedPersons
			@FromDate datetime,
			@ToDate datetime
AS

SET NOCOUNT ON

SELECT	p.*
FROM	Persons p
	LEFT JOIN (
		SELECT	PersonId
		FROM	Documents 
		WHERE	DocumentDate >= @FromDate 
			AND DocumentDate < DATEADD(DAY, 1, @ToDate)
		GROUP BY
			PersonId) d ON p.PersonId = d.PersonId
WHERE	d.PersonID IS NULL

Open in new window

Alpesh Patel

select PersonName
from PersonTable inner join DocumantTable on PersonTable.PersonId = DocumantTable.PersonId
where DocumentDate between YourStartDate and YourLastDate

Kalyanum Deepak Kumar

select PersonName
from PersonTable
where PersonId not IN(select PersonId from documantTable
and DocumentDate between YourStartDate and YourLastDate)

Pretty simple.

RBS

ASKER

Thanks - quick - exactly what I needed. Missing "Top" in 1

r.

Anthony Perkins

>>Missing "Top" in 1<<
There is simply not need for that. It is redundant.

RBS

ASKER

thanks - guess i had another error in my actual query and thought it was lack of "top" doing it. Will try without

r.