Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
DBISAM to EDB - Date Interval Aggregate? |
Mon, Feb 22 2010 8:28 AM | Permanent Link |
durumdara | 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 |
Mon, Feb 22 2010 9:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Feb 22 2010 10:23 AM | Permanent Link |
durumdara | 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 |
Mon, Feb 22 2010 11:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | durumdara
Try AVG(CAST(CAST(_bill_pay - bill_date AS INTEGER) AS FLOAT)) AS xAVG, Roy Lambert |
Tue, Feb 23 2010 5:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Tue, Feb 23 2010 5:26 PM | Permanent Link |
Richard Harding | 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 |
Tue, Feb 23 2010 5:39 PM | Permanent Link |
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 |
Wed, Feb 24 2010 3:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |