Need help on date array to process records

[meryls]

I am using Crystal 9.

I have been asked to write a report based on records that have the following data:

charge_amount
tax_amount
charge_date
charge_start_date
Charge_end_date
.
.
.


Some charges are for a single date. When that is true, charge_date is populated. Some charges are for a rental that spans some range of dates. When that is true, the charge_start_date and charge_end_date are populated.

I have been asked to create a report that sorts, by date, the sum of the charges and taxes. So, if the date range is from July 10, 2005 through July 20, 2005, I would take the charge_amount and divide it by the 11 days and get an amount per day. I then need to add this to the sum for each of these days July 10, July 11, July 12, etc. I need to also do this for the tax amount.

I am not sure how to create this array. I will not have any knowledge of the dates of these charges until I am processing the records. I will need to print the totals sorted by date. Ideally, I would have each date drill-down to the individual records I recieved, but I'm not sure if that part is possible.

Can anyone give me help with this? I'm not sure how to create such an array.

Thanks in advance!

Meryl

Thanks very much.

 

[synapsevampire]

How large might this date range be? A week, 10 years, or?

What will the output look like? If it's just an amount it may not be so bad, but I'd suggest doing everything possible to ensure that soon your dba is back biting the heads off of goat ticks for a living, as this design for a table is just plain baaaaaad, and lazy.

I would also suggest trying to create a Stored Procedure for this as the processing is relatively complicated. A shame you didn't post the database and version, nor expected output...

If you insist on doing so in Crystal, and the amount of dates is somewhat manageable, I would create an array to hold the values only, and keep track of which array subscript to add each amount to by using the maximum({?MyDateRange}) - {table.date})

Fairly tricky stuff, and I don't have time right now, and since you didn't post specifics, I'd prefer that you do so prior to starting.

-k

 

[meryls]

My data is actually coming to me via a ttx file -- I don't access the database directly. Crystal is invoked from a vb application that passes a recordset. This is because the app could be running on top of different databases, for a variety of reasons.

The data range could be anything, as the user will enter any range of dates desired.

As far as output, this is what is being asked, assuming the earliest date is 2/3/2005 and the latest date is 2/5/2005.

Date Total Charges Total Taxes
------- -------------- -----------
2/3/2005 2,302.00 58.23
2/4/2005 457.00 7.67
2/5/2005 1,098.68 14.12

Thanks, again. If this is too complex for Crystal, I may have to ask the VB developer to provide a different set of data.

Meryl