Variable question

[tierdog]

I am attempting to write a report that will calculate the number of days billed for each client for a one year time frame. The problem I have is that each month there is a report run that totals just that month. These reports have not been kept so cannot just look at them and add them up. The database field on the Current table that has the start date is changed each month to reflect billing for just that month. (ie February billing would say 020107 through 030107) There is a table (Tracking) that tracks all changes to each account. On that table I can go back until I find the entry with the earliest start date and that is the real start date, (this is not always the first entry due to data corrections) but how do I load that value into a variable that can be used in the calculations? The fields needed are Current.startdate, Current.enddate, Tracking.startdate, Tracking.enddate. To make things a little more complicated, the Current table fields are both DateTime fields but on the Tracking table the Tracking.start field is a DateTime field and the Tracking.enddate field is a Number type field. I am using Crystal XI to access an Oracle database.
Any help provided will be much appreciated.
JC

 

[dgillz]

What is the format of the number field?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[tierdog]

The Tracking.enddate field is a Number field in the format "123,456". Not sure if this is an error in the database setup or not. Tracking.startdate is DateTime in type.
JC

 

[dgillz]

Any ideas how to translate 123456 into a date?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[tierdog]

I contacted the Database folks and they confirmed it is an error in the setup of the table. They are going to correct ASAP. My biggest concern is trying to find the correct starting point. Any ideas on this?

JC