Using DATEDIFF() with MAXDATE() in WHERE clause

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

General SQL Server Forums

New to SQL Server Programming

Using DATEDIFF() with MAXDATE() in WHERE clause

Author

Topic

Sam Spade
Starting Member

3 Posts

Posted - 2015-03-06 : 12:29:41

Hey guys, I've been on this problem for a couple hours with no luck. I'm hoping someone here can help me. This is my first time on here so hopefully I'll be able to explain this well enough.

I'm working in an enrollment table where members have multiple enrollment segments that determine whether they are currently enrolled or not. All historical enrollment segments are listed as separate rows and include an effective date and termination date for that segment. If the member is currently enrolled, one of the enrollment segments will have an enrollment date of 12/31/2078, and this will always be the highest term date listed. The relevant column names are enrollid, effdate, termdate, memid. The enrollid is the primary key unique to each row, for each separate enrollment segment. The memid is a unique member identifier listed in the member table, and this is how the two tables are joined. There's virtually an infinite number of enrollment segments and date configurations possible among our membership. If I wanted to display all of the enrollment segments for a given member, the code would be:

select *
from enrollkeys e
inner join member m on m.memid = e.memid

To illustrate, a member may have been enrolled from 01/01/2010 through 12/31/2011, this would show as it's own row with a unique enrollid. Then there may be another row for the same member if they were enrolled from 01/01/2011 through present, again with a unique enrollid. Notice that because the termdate from the older segment is on the day before the effdate on the next one, this member would NOT have had a lapse in membership.

I have to select only members who do not show a single gap of enrollment (time when they were not enrolled) of 45 days or more to date in 2015. Because the member must be active, I know that I need to look in the enrollment segment that has the highest termdate, ie MAX(e.termdate). My thinking is that as long as the difference between the MAX(e.termdate) and the same e.effdate for that enrollment segment is >= 45 days, then I'm good. The tricky part is using this in the where clause. I just can't get it to work.

Here's what I have. I won't include the select, from, or joins, I don't think they're relevant.

where datediff(
dd,
(select e.effdate from enrollkeys e where e.memid = m.memid and e.termdate in
(select MAX(e.termdate) from enrollkeys e where e.memid= m.memid)),
(select MAX(e.termdate) from enrollkeys e where e.memid = m.memid)) >= 45

To break out the DATEDIFF arguments:
Argument 1 (data format): dd
Argument 2 (beginning date):
(select e.effdate from enrollkeys e where e.memid = m.memid and e.termdate in
(select MAX(e.termdate) from enrollkeys e where e.memid= m.memid))
Argument 3 (end date): (select MAX(e.termdate) from enrollkeys e where e.memid = m.memid))

The error message I get is that the subquery (unsure which one, exactly) produces more than one result. When I test this in a separate select statement, it pulls the datediff for each enrollment segment, resulting in duplicate results, so SQL Server doesn't know which one to say is > 45. In a standalone select statement, I can just add distinct to the beginning to get only one result, but it seems I can't do this when I have it in my WHERE clause.

I feel I'm so close, but just can't get the thing to work. Any thoughts? Sorry for the long-winded explanation, hopefully it has all the info needed. Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-06 : 14:16:45

Are you using SQL Server 2012 or later?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Sam Spade
Starting Member

3 Posts

Posted - 2015-03-06 : 14:39:52

I'm using Management Studio 2008 R2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-07 : 04:46:59

Doesn't say much. You can use SSMS 2008 to connect to a SQL Server 2012 database server.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Sam Spade
Starting Member

3 Posts

Posted - 2015-03-07 : 15:41:08

Oh. To be honest I'm not sure, but I'd wager it's 2008, we're not particularly up to date on our infrastructure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-07 : 16:55:09

[code]SELECT m.*
FROM dbo.Member AS m
CROSS APPLY (
SELECT TOP(1) e.TermDate,
e.EffDate
FROM dbo.EnrollKeys AS e
WHERE e.MemID = m.MemID
ORDER BY e.TermDate DESC
) AS e
WHERE DATEDIFF(DAY, e.EffDate, e.TermDate) >= 45;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:57:12

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources