We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

RBS
RBS asked
on
Medium Priority
197 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

CERTIFIED EXPERT
Top Expert 2012

Commented:
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 PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
select PersonName
from PersonTable inner join DocumantTable on PersonTable.PersonId = DocumantTable.PersonId
where DocumentDate between YourStartDate and YourLastDate
Kalyanum Deepak KumarSenior Technical Lead

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

Pretty simple.
RBS

Author

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

r.
CERTIFIED EXPERT
Top Expert 2012

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

Author

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

r.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.