Dates - selecting depending on sequence

[sdempsey]

Hi,

I am trying to compare dates. So for example I could have the following dates:-

Person A
09/09/2006
09/10/2007
09/10/2007
09/10/2008

Person B
or I could have the following
09/10/2007
09/10/2008

If ther are only 2 dates present for a person I would like 09/10/2007 to be taken as the start date. However my problem occurs if there is more than 2 dates. I would like the following to occur:-

09/10/2007 (as the first date to used to start a compare.

Therefore I would end up with the following:-
First Date Last Date
Person A 09/10/2007 09/10/2008
09/10/2007 09/10/2008

Thanks in advance

Sarah

 

[r937]

it's not at all clear what you want, but here is sort of what you said --

select person
     , case when count(*) = 2
            then '09/10/2007'
            else min(startdate) 
         end  as [First Date]
     , max(startdate) as [Last Date]
  from daTable
group by person

r937.com | rudy.ca

 

[sdempsey]

Hi,

Thanks for your reply.

I will try to be a bit more clear.

I have over 100 employees in a database and I would like the query return 100 employees with a First Date and Last Date. The first date we have for each employee tends to have little data attached to it and would only want to use this if we needed to.

If only 2 dates recorded against an employee - the min as their start date and max as their end date.

If there are 3 or more dates recorded against an employee - the min date (is not actaully the min date but the one after the min date), this would then become their min date. As this will have more info recorded against it. The max date will still be the maximum date.

Thanks

 

[r937]

something like this, then --

select person
     , case when count(*) > 2
            then 
  ( select min(startdate)
      from daTable
     where startdate > 
           ( select min(startdate)
               from daTable
              where person = X.person ) )
            else min(startdate) 
         end  as [First Date]
     , max(startdate) as [Last Date]
  from daTable as X
group by person

r937.com | rudy.ca

 

[vongrunt]

For brain exercise, let's try architectural approach

This:

select person, min(startdate) as mindate
from daTable
group by person
having count(distinct startdate) > 2

... returns only persons+maxdates for persons with more than two distinct entries. I'm not sure about DISTINCT though (sample data has some dupes) - your choice. This:

select A.person, min(A.startdate), max(A.startdate)
from daTable A
left outer join 
(	<query from above>
) B
on A.person = B.person
[!]where A.startdate > B.mindate or B.mindate is null[/!]
group by A.person

... joins back on same table, optionally skipping 1st min value for person with more than 2 entries. Highlighted line is the key here...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]