Dear Support!

My problem is that:

I want to port my DBISAM application that uses this code:

select
  partner_code,
  avg(bill_pay-bill_date) as avgday from bill_head
group by partner_code

So we want to see (average) how many days between bill's date and the real paying.
This may be negative.

The DBISAM version is uses FloatFIeld on avgday field, and it is working.
(5 days + 10 days)/2 bills = 7.5

But when I use this code on EDB, I got error that result is TLargeInt field.

I tried to cast the result, but I got error:

ElevateDB Error #700 An error was found in the statement at line 10 and column 9 (Expected
Char, VarChar, Byte, VarByte, Boolean, SmallInt, Integer, BigInt, Float, or Decimal
expression but instead found AVG(bill_pay-bill_date))

Hmmmm...?

How can I force the AVG to compute double/float result?

Thanks:
   dd

durumdara


Its a bit difficult from what you've posted but I'm guessing that bill_pay and bill_date are date fields in which case welcome to the wonderful world of INTERVAL.

What you'll need is this

partner_code,
  avg(bill_pay-bill_date DAYS) as avgday from bill_head
group by partner_code

which tells ElevateDB that you want the interval calculated as days.

Roy Lambert [Team Elevate]

Dear Roy!

Sorry, but your example isn't working for me... What is the good SQL statement?

<<<Roy Lambert wrote:
durumdara
Its a bit difficult from what you've posted but I'm guessing that bill_pay and bill_date are date fields in which case
welcome to the wonderful world of INTERVAL.>>>

Yes, two dates of the bill (the date is creation date, the pay is when we got the money for it).


<<<partner_code,
  avg(bill_pay-bill_date DAYS) as avgday from bill_head
group by partner_code>>>

This is not working. I tried many variations, but nothing happened.

Thanks:
  dd

durumdara



Try

AVG(CAST(CAST(_bill_pay - bill_date AS INTEGER) AS FLOAT)) AS xAVG,


Roy Lambert

<< This is not working. I tried many variations, but nothing happened. >>

What do you mean when you say it isn't working ?  It works fine here, and
returns the avg of the number of days, as expected.

--
Tim Young
Elevate Software
www.elevatesoft.com

durumdara

>>
select
 partner_code,
 avg(bill_pay-bill_date) as avgday from bill_head
group by partner_code

So we want to see (average) how many days between bill's date and the real paying.
This may be negative.<<


Assuming that there is a DATE field bill_date, you can get the average number of dates
between bill date and pay date by:

SELECT
 partner_code,
 avg(bill_date - bill_ pay-bill_date) AS DaysToPay FROM bill_head
GROUP BY partner_code


Richard Harding

Richard Harding wrote:

durumdara

>>
select
 partner_code,
 avg(bill_pay-bill_date) as avgday from bill_head
group by partner_code


So we want to see (average) how many days between bill's date and the real paying.
This may be negative.<<

I did not read the original post correctly.  What is the relevant definition of bill_head?
My response was essentially the same as yours which should work.


Richard Harding

Tim

I think the problem is that a straight AVG(date1-date2) returns an integer and he wants a float. The only way I could come up with is the rather ghastly piece of SQL I posted.

eg

select avg(_dateadded - _datebecamecandidate),
AVG(CAST(CAST(_dateadded - _datebecamecandidate AS INTEGER) AS FLOAT)) from calls

returns

1664  1664.14379084967

Roy Lambert