Problem with DateDiff function with different scenarioes 1

[liliya13]

Hi,

I need to create a query which extracts just

1. those members who were enrolled for at least 90 days anytime starting from Sep1,2003 till the Apr30/2004. (However they may be enrolled before Sep1 and enrollment end date migh happen before Apr 30.
2. The part of their enrollment should take place after Sept 30

I did the following query:
SELECT *
FROM mytable
WHERE DateDiff("d",[mytable].[startdtSept1],([mytable].[program end date])>=90 and DateDiff("d",[mytable].[startdtSept30],([mytable].[program end date])>0 and DateDiff("d",[mytable].[program start date],[mytable].[startdtSept1])>=0;


However I found out that some members are extracted whose: 1.program start date is 10/1/2003
2.and program end date 10/31/2003

They should not be in the file as they do not meet the conditions. As I learned before, Datediff
function assumes the earlier date goes first..but although for most of them PROGRAM END DATE is later than SEPT1....in some cases PROGRAM END DATE goes BEFORE... as well as sometimes START DATE is after SEPT1

How I can adjust my query in order it take into account such a scenario? Probably I cannot do it with DateDiff()?

I have beaten my head...and have no clue...
Could you please give me a hand? It would be greatly appreciated...

Thank you in advance

 

[PHV]

In mytable, what are the meaning of startdtSept1, program end date, startdtSept30 and program start date ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[liliya13]

1.Most of records under PROGRAM END DATE have values
4/30/2004 while some of them have earlier dates such as 10/31/2003, 11/31/2003 (in other words they can end in 2003)

2. PROGRAM START DATE may be whatever date . They can start 2001 ( as it is a beginning of enrollment).

For the purpose of my query I created additional fields in my Table:
3.StartdtSept1 and populated all records with the same value 9/1/2003

4.StartdtSept20 and populated all records with 9/30/2003

All date have date/time short format...

 

[PHV]

Something like this ?
WHERE [program end date]-[startdtSept1]>=90
AND startdtSept30 Between [program start date] And [program end date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244