Re: Problem with query
Date: Sat, 14 Jul 2001 08:29:01 -0500
Message-ID: <0RX37.324$np6.368471_at_nnrp3.sbc.net>
SELECT r.CUSTOMER_ID AS CustomerID
, AVG(DECODE(SIGN(r.LAST_PAID_DATE-r.INVOICE_DATE)
,1,r.LAST_PAID_DATE-r.INVOICE_DATE
,NULL,TO_NUMBER(NULL),0)) As AvgPayDays
FROM RECEIVABLE r
WHERE r.TYPE = 'I'
AND r.STATUS = 'A'
GROUP BY r.CUSTOMER_ID
"Mark" <flatious_at_aol.com> wrote in message
news:9mhuktsnj5foopv3hgn56va86hbcl5q8bs_at_4ax.com...
> I would like to apologize in advance if I am not in the proper
> newsgroup.
>
> At any rate--on an Oracle 7.3 database, I tried to use the following
> query:
>
> SELECT PD.CustomerID,
> Avg(PD.PayDays) AS AvgPayDays
>
> FROM (
>
> SELECT R.CUSTOMER_ID AS CustomerID,
> R.LAST_PAID_DATE - R.INVOICE_DATE AS PayDays
>
> FROM RECEIVABLE R
>
> WHERE R.LAST_PAID_DATE > R.INVOICE_DATE AND
> R.TYPE = 'I' AND
> R.STATUS = 'A'
>
> UNION ALL SELECT R.CUSTOMER_ID AS CustomerID,
> 0 AS PayDays
>
> FROM RECEIVABLE R
>
> WHERE R.LAST_PAID_DATE <= R.INVOICE_DATE AND
> R.TYPE = 'I' AND
> R.STATUS = 'A'
>
> ) PD
>
> GROUP BY PD.CustomerID
>
> Obviously, I am trying average the difference of an invoice-date and a
> pay-date but I want prepaid invoices to be averaged as a zero.
>
> Obviously, I am stupid. I have no idea how to do this any other way;
> please enlighten me.
>
>
>
Received on Sat Jul 14 2001 - 15:29:01 CEST