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
RBSAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

select PersonName
from PersonTable
where not exists (select 1 from DocumantTable
                             where DocumentTable.PersonId = PersonTable.PersonId
                             and DocumentDate between YourStartDate and YourLastDate)
0
 
Anthony PerkinsCommented:
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

0
 
Anthony PerkinsCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Alpesh PatelAssistant ConsultantCommented:
select PersonName
from PersonTable inner join DocumantTable on PersonTable.PersonId = DocumantTable.PersonId
where DocumentDate between YourStartDate and YourLastDate
0
 
Kalyanum Deepak KumarSenior Technical LeadCommented:
select PersonName
from PersonTable
where PersonId not IN(select PersonId from documantTable
                                                          and DocumentDate between YourStartDate and YourLastDate)

Pretty simple.
0
 
RBSAuthor Commented:
Thanks - quick - exactly what I needed.  Missing "Top" in 1

r.
0
 
Anthony PerkinsCommented:
>>Missing "Top" in 1<<
There is simply not need for that.  It is redundant.
0
 
RBSAuthor Commented:
thanks - guess i had another error in my actual query and thought it was lack of "top" doing it.  Will try without

r.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.