• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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
0
RBS
Asked:
RBS
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now