Help with Query 1

[fmrock]

I have the following Query I was hoping you guys could give me some ideas on getting the last 2 columns I need.

The columns I need help coming up with are "Active_AR_Balance" AND "Average Days Out (ADO)"

The Active_AR_Balance is everything up though that Month.

The average days out is the AR Balance divided by payments posted in the last 90 days (or 3 months)

Any help or ideas would be great because I am a bit Stumped.

Thanks,

SELECT 
	C.CPCODE "Group_Num"
	, L.LOCNAME "Site_Name"	
	,trunc(to_date(C.POSTDATE,'j'),'MONTH') "Month"
	,SUM(CASE WHEN (C.TYPE='C') THEN C.AMOUNT ELSE 0 END) "Gross_Charges"
	,SUM(CASE WHEN (C.TYPE='P') THEN C.AMOUNT ELSE 0 END) "Gross_Payments"
	,SUM(CASE WHEN (C.TYPE='A') THEN C.AMOUNT ELSE 0 END) "Gross_Adjustments"
	,0 "Active_AR_Balance"
	,0 "Average Days Out (ADO)"
FROM 
	MEDCHARGES C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
WHERE
	(C.CPCODE IN ('1','2','3'))
	AND (C.SPLITFLAG IS NULL)	
	AND (C.POSTDATE IS NOT NULL)
	AND (C.POSTDATE <= to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))
GROUP BY
	C.CPCODE
	, L.LOCNAME
	,trunc(to_date(C.POSTDATE,'j'),'MONTH')

 

[Dagon]

I have no idea (and I doubt if anyone else has either) what you mean by "The Active_AR_Balance is everything up though that Month.". Is that the sum of something? What exactly?

For Oracle-related work, contact me through Linked-In.

 

[fmrock]

Sorry, what I mean by Active AR would be the sum of C.AMOUNT for everything with a postdate for that months grouping and and before.

 

[Dagon]

You'll probably need to move away from using simple group by to using analytic functions. Using a slightly simplified model, you could do something like this:

create table medcharges (cpcode number, postdate date, amount number, type varchar2(1));

insert into medcharges values (1, add_months(trunc(sysdate), -1), -50, 'C'); 
insert into medcharges values (1, add_months(trunc(sysdate), -1), 50, 'P');
insert into medcharges values (1, add_months(trunc(sysdate), -1), 11, 'P');
insert into medcharges values (1, add_months(trunc(sysdate), -2), -55, 'C');
insert into medcharges values (1, add_months(trunc(sysdate), -3), 38, 'P');
insert into medcharges values (1, add_months(trunc(sysdate), -3), 13, 'P');
insert into medcharges values (1, add_months(trunc(sysdate), -4), -44, 'C'); 
insert into medcharges values (1, add_months(trunc(sysdate), -5), -12, 'C');
insert into medcharges values (1, add_months(trunc(sysdate), -5), 91, 'P');     

select cpcode, month, charge, pay, balance, num_payments, balance/num_payments as av_days_out
from
(select cpcode,
          trunc(postdate, 'month') month,
         sum(case when type = 'C' then amount end) over (partition by cpcode, trunc(postdate, 'month')) charge,
         sum(case when type = 'P' then amount end) over (partition by cpcode, trunc(postdate, 'month')) pay,
         sum(amount) over (partition by cpcode) as balance,
         row_number() over (partition by cpcode, trunc(postdate, 'month') order by rowid) as rn,
         count(case when type ='P' and postdate >= add_months(trunc(sysdate), -3) then amount end) over (partition by cpcode) as num_payments
from medcharges)
where rn=1
order by cpcode, month

I'm not totally sure about the av_days_out since I don't really understand what that is supposed to be.

For Oracle-related work, contact me through Linked-In.

 

[fmrock]

Thanks for your help, I have made some progress on this query. Hopefully next week I can get it all together.

 

[fmrock]

Still not coming up with the right number for payment for the last 3 months.

For example, for each month I want a summary of Type=P for that month and 2 previous months.

So if the Grouping was March 2010 I would want a summary of Jan, Feb, March 2010

April 2010 would be a summary of Feb, March, April

And so on.

Any ideas?

 

[fmrock]

I tried both of these and this did not work.

,SUM(CASE WHEN ((C.TYPE='P') AND (C.POSTDATE >= to_Number(to_char(add_months(trunc(TO_DATE(C.POSTDATE,'J'),'MONTH'),-3),'J'))) AND (C.POSTDATE < to_Number(to_char(add_months(trunc(TO_DATE(C.POSTDATE,'J'),'MONTH'),1),'J')))) THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME) Payments_Last_3_Months
	



,SUM(CASE WHEN (C.TYPE='P') THEN C.AMOUNT ELSE 0 END) OVER (PARTITION BY C.CPCODE,L.LOCNAME,trunc(to_date(C.POSTDATE,'j'),'MONTH') ORDER BY C.CPCODE,L.LOCNAME,trunc(to_date(C.POSTDATE,'j'),'MONTH') ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) Payments_Last_3_Months


[code]

 

[Dagon]

I'm not sure if you want the number of payments or the total amount. If it's the number of payments, you'd just want a count rather than sum of amount.

Either way, what you'll need to do is to specify a window over which the value is to be summed. Using the data I had before, you could do something like this:

select cpcode, 
         month, 
         charge, 
         pay, 
         num_pay,
         sum(num_pay) over (partition by cpcode order by month rows between 2 preceding and current row) as num_pay_qtr,
         sum(pay) over (partition by cpcode order by month rows between 2 preceding and current row) as qtr_pay
from
(select cpcode,
          trunc(postdate, 'month') month,
         sum(case when type = 'C' then amount end) over (partition by cpcode, trunc(postdate, 'month')) charge,
         sum(case when type = 'P' then amount end) over (partition by cpcode, trunc(postdate, 'month')) pay,
         count(case when type = 'P' then 1 end) over (partition by cpcode, trunc(postdate, 'month')) num_pay,
         sum(amount) over (partition by cpcode) as balance,
         row_number() over (partition by cpcode, trunc(postdate, 'month') order by rowid) as rn
from medcharges)
where rn=1
order by cpcode, month



    CPCODE MONTH         CHARGE        PAY    NUM_PAY NUM_PAY_QTR    QTR_PAY
---------- --------- ---------- ---------- ---------- ----------- ----------
         1 01-JUL-10        -12         91          1           1         91
         1 01-AUG-10        -44                     0           1         91
         1 01-SEP-10                    51          2           3        142
         1 01-OCT-10        -55                     0           2         51
         1 01-NOV-10        -50         61          2           4        112

For Oracle-related work, contact me through Linked-In.

 

[fmrock]

Dagon, Thanks for your help, I got this exactly how I wanted.

I wanted the sum and not the count.