Help using null/blank datetime field in calculation 2

[techwriterAR]

I need to calculate the number of days a person has been a member of a department to determine seniority. The data comes from an SQL2000 database and I am using Crystal Reports v8.5.

The fields include employee number, department, date_in, date_out. The date fields are datetime. The problem I am having is that some of the date_out fields are blank/null which means that the employee is still a member of that department and I will need to use the current date.

The following formula works when both datetime fields have entries:

datetimevar start:={DPT_MBRS.DATE_IN};
datetimevar end:={DPT_MBRS.DATE_OUT};
datediff('d',start,end);

I do not know how to write the formula to use the current datetime if the date_out field is blank/null.

I know that I can get what I want in SQL by using the following formula but I have not been able to reproduce this formula in Crystal.

Sum(datediff(day, date_in, ISNULL(date_out, getdate())))

All help will be greatly appreciated.
Thanks,
gjeffcoat

 

[noxum]

datetimevar start:={DPT_MBRS.DATE_IN};
datetimevar end;

if isnull({DPT_MBRS.DATE_OUT}) then
end := today
else
end:={DPT_MBRS.DATE_OUT}

datediff('d',start,end)


Noxum

 

[synapsevampire]

The best solution is the one you already know:

Create a SQL Expression containing:

datediff(getdate(), date_in, ISNULL(date_out, getdate()))

Now the server will return the number for you and you can use the field in a traditional sum() in CR.

-k

 

[techwriterAR]

Thank you noxum! The formula works great.

And thanks for responding, synapsevampire. I know I could have built my report by importing an SQL query into Crystal's query builder. What I was hoping to do was learn a little bit more about using crystal instead. Another question has occurred to me though. Is it possible to write an SQL statement inside Crystal Reports itself (without having to build the report on a query) that does the same thing noxum's formula does?

Anyway, thanks to both of you for responding.

gjeffcoat

 

[synapsevampire]

If you have CR 9 you can write real SQL.

The solution I offered doesn't use Crystal's Query builder, and I suggest you not use it as it's a dying technology. Consider building Views or SP's.

The following passes the SQL to the database in the select line, hence it does what you want, only it's faster because the database does the work.

A slight error in my original post, my apologies:

datediff(d, datein, ISNULL(dateout, getdate()))

Musta been too late or early in the day...

-k

 

[techwriterAR]

Okay, synapsevampire! Way to go. Not only does this work, but I learned how to do something BRAND NEW!! This is fantastic. I have never used "Insert | Field Object |Sql Expression Fields"; did not know anything about using them. I thought you had to write the entire report in a query if you were using an SQL statement. WOW this is great.

Thanks much!
gjeffcoat

 

[synapsevampire]

Yeah, I leverage them constantly, you should see a nice performance imporvement by using them.

Of course in CR 9 you can use big kid SQL so this problem will go away

-k

 

[techwriterAR]

Unfortunately, I will probably not be able to use CR9. I did upgrade to it from 8.5 but reports I created for programs written in-house (using Borland's Delphi) caused problems with these programs. I do not remember the exact problem(s), just that the programs did not work correctly. So I went back to 8.5 and, for extra insurance, I convert any reports I write for the in-house programs to version 7. Our programs work fine when we do this. But I am very happy that I learned about writing an SQL statement and inserting it into a report. I think this will come in very handy.

Again, I appreciate your help.

gjeffcoat