DateDiff formula query 1

[kutz13]

I'm using CR8.5 against an Oracle 8 DB procedure and need help with the following if possible.
Data records contain a start date and an end date(say D1 & D2), I need to include all records that have an end date that is either Null or ends within user defined period(start & finish), I then need to calculate the total of days falling within the period from each record.
I tried along the lines of:
If {D1} >= {start} and IsNull({D2}) then DateDiff("d",{D1},{finish}) etc.........
The formula returns data for records with dates within period but not for records with Null end dates or start dates prior to period start.
Any advice on where I'm going wrong? Thanks.

 

[Turkbear]

Hi,
Probaby need an OR statement fror the recoprd selection: IsNUll({D2})
OR
{D2} In {start} to {end}


Then create your DateDiff formula separately to do the calculations( remembering to exclude the Null end dates)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think for record selection you should use:

isnull({table.enddate}) or
{table.enddate} > {?start}

This way you won't exclude records where the enddate is not null but is later than the report end date. Then create a formula like the following for your detail section:

if isnull({table.enddate}) or
{table.enddate} > {?enddate} then
datediff("d",{table.startdate},{?enddate}) else
datediff("d",{table.startdate},{table.enddate})

This would use the end date of the report when the end date was null or later than the report end date.

-LB

 

[kutz13]

The formula in full is:
if {table.startdate} < {D1} and {table.enddate} <= {D2}
then DateDiff("d",{D1},{table.enddate})
else
if {table.startdate} >= {D1} and {table.enddate} <= {D2}
then DateDiff("d",{table.startdate},{table.enddate)
else
if {table.startdate} < {D1} and ({table.enddate} > {D2} or
IsNull({tabelenddate}))
then DateDiff("d",{D1},{D2})
else
if {table.startdate} >= {D1} and ({table.enddate} > {D2} or IsNull({table.enddate}))
then DateDiff("d",{table.startdate},{D2})

I was hoping therefore that I could return data from historic periods as well then. But no joy!

 

[lbass]

You have to test for nulls first. Try changing your formula to the following (I'm assuming that D1 and D2 are parameters and that D1 is a start date, and D2 and end date):

If IsNull({table.enddate}) and
{table.startdate} < {?D1} then
DateDiff("d",{?D1},{?D2}) else

if IsNull({table.enddate}) and
{table.startdate} >= {?D1} then
DateDiff("d",{table.startdate},{?D2}) else

if {table.startdate} < {?D1} and
{table.enddate} <= {?D2} then
DateDiff("d",{?D1},{table.enddate}) else

if {table.startdate} >= {?D1} and
{table.enddate} <= {?D2} then
DateDiff("d",{table.startdate},{table.enddate}) else

if {table.startdate} < {?D1} and
{table.enddate} > {?D2} then
DateDiff("d",{?D1},{?D2}) else

if {table.startdate} >= {?D1} and
{table.enddate} > {?D2} then
DateDiff("d",{table.startdate},{?D2})

You wouldn't use the record selection formula that I suggested in my earlier post.

-LB

 

[kutz13]

That did the trick - thanks for the help lbass