Difference between 2 dates - with a twist!

[Corky]

Hi,

I have been racking my brains how to sort this one!

eg A
APPOINTMENT DATE Difference in Days
20-Nov-03 0
29-Jan-04 70
01-Apr-04 63
24-Jun-04 84

eg B
APPOINTMENT DATE Difference in Days
20-Nov-03
29-Jan-04 70
07-Jun-04 130
27-Jul-04 50

Above demonstrates some data i have. If there is a difference of
greater than 121 days (4 months) between 2 dates i want the first date
to be pulled out, so in example B I need 29-Jan-04 to be selected. If
the difference between any of the dates is not greater than 121 days,
then i need the last date selecting. In example A, this would be
29-Jun-04.

I have tried several approaches all of which havent worked. I ranked
the dates in order then pulled them into a crosstab. I have then tried
to datediff between each of the ranked dates, but this just seem too
complicated.

Has anyone got any ideas?

 

[Jerry Whittle]

Example A doesn't have a 29-Jun-04; however, I'm guessing this is what you
mean:

SELECT TOP 1 IIf([DIFFERENCE IN DAYS]>121,
[APPOINTMENT DATE]-[DIFFERENCE IN DAYS],
[APPOINTMENT DATE]) AS TopDate,
Corky.[DIFFERENCE IN DAYS]
FROM Corky
ORDER BY Corky.[DIFFERENCE IN DAYS] DESC;

 

[Corky]

Thanks, yes for example A, i meant the 24-Jun. That may work, but
probably what i should have said is that i haven't yet calculated the
difference in days. that is the bit i am stuck on first, because the
dates are between rows.

 

[Saran]

Dear Corky,

I created a sample table with three fields ID, AppDate, DateDiff
and entered your sample data where ID is 'A' for your e.g., A and 'B'
for e.g, B

Below query would run against it and fetch your expected result. I
assume your requirement is if difference is not greater than 121 pick
latest date, else earliest. Ignoring 0 date difference rows.

(Select top 1 T3.* from testtable T3 left join (
Select T1.ID from TestTable T1 inner join TestTable T2 on
Int(T2.DateDiff-T1.DateDiff) > 121 and T1.ID = T2.ID) T12 On T12.ID =
T3.ID
Where T12.ID is null and datediff <> 0 Order By T3.AppDate Desc)
Union All
(Select top 1 T3.* from TestTable T3 inner join
(Select T1.ID from TestTable T1 inner join TestTable T2 on
Int(T2.DateDiff-T1.DateDiff) > 121 and T1.ID = T2.ID) T12
On T12.ID = T3.ID Where T3.DateDiff <> 0 Order By T3.AppDate Asc)

Hope it would help.

Thanks,
Saran.

 

[Corky]

Thanks, i kind of did this, but i got what i needed in the end anyway,
many thanks.