Link to home
Start Free TrialLog in
Avatar of gsgi
gsgiFlag for United States of America

asked on

Can sql do this?

Ok, so I admit that I impressed with you people in this SQL forum.  You guys seem to make SQL do everything.  Can it do this?
At some point I would expect to be told "dump the data and write a small program to do that!"

Table a
1/1/00 Patien11 100.00
1/2/00 Patient1 -100.00

1/1/01 Patient1  500.00
3/31/01 Patient1 -300.00
6/31/01 Patient1 -100.00
12/31/01 Patient1 -100.00

1/1/02 Patient1 100.00
3/1/02 Patient1 -100.00

1/1/03 Patient1 100.00
4/1/03 Patient1 -100.00

1/1/05 Patient1 100.00
2/1/05 Patient1 100.00
3/1/05 Patient1 -100.00
4/1/05 Patient1 100.00
5/1/05 Patient1 100.00


On any given day we might want to know this about patient1:
A. What is the average # of days it took for patient#1 to pay off his balance the last three times he had a balance.
Roughly in '01 - 365 days : in '02 - 60 days : in '03 90 days so roughly  515/3 days

B. What is the average amount patient1 owed over the # of days patient1 had a balance.

C. Pretend it is 6/15/05.  Can you give an aging of patient1's account? i.e.
0-30 : 0  31-60: 100.00 61-90: 100.00 91-120: 0  121-150:100.00  151-180:0  181-210:0  211-240:0  241-270:0  271-300:0 over300: 0

Thanks,
gsgi

SOLUTION
Avatar of dduser
dduser

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What is the average amount patient1 owed over the # of days patient1 had a balance
>> whats the expected result...in the example given...


for the next question also explain how the o/p is calculated...
i wud try 2 do tat in sql then...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gsgi

ASKER

Ok, in question 1 you are using the years which may actually work.  I set up the example using different years each time patient1 had a balance and paid it off just to make the example more simple.  But some patients could have have had three balances with us and paid them off within a matter of a month.  Below is a patient that pays immediately.  I think it is going to be very hard to write a sql query to 'figure out' where to start - i.e. how to tell the date where the patient had a balance begin x # of cycles of having a balance ago - in this case where I choose x to be 3 cycles of billing ago.  So below, the third to last billing cycle would begin on 1/12/06.  

1/1/06 patient2 50.00
1/1/06 patinet2 -50.00
1/12/06 patient2 100.00
1/12/06 patient2 -100.00
1/15/06 patient2 250.00
1/15/06 patient2 -250.00
1/23/06 patient2 169.00
1/23/06 patinet2 -169.00

It is probable though, that your example code looking at this by year will yield close results anyway to what I want, so I'll use it as a great suggestion.

As for #2 from the first example above, the last three billing cycles started on 1/1/01, so:
In '01: for 60 days he owed $500; for 90 days he owed $200; for 180 days he owed $100
In '02: for 60 days he owed $100.00;
In '03: for 90 days he owed $100.00;

So, 60 x 500 + 90 x 200 + 90 x 100 + 60 x 100 + 90 x 100 / ( 60 + 90 + 180 + 60 + 90) = 184.54



Avatar of nmcdermaid
nmcdermaid

... I just jumped in from your other queston.

A calendar table makes a lot of these time based calcs easier.


I'm not sure I understand how you calculate how long a billing cycle is.



I don't really understand this:

>> how to tell the date where the patient had a balance begin x # of cycles of having a balance ago

Avatar of gsgi

ASKER

x # of cycles of having a balance ago

x=1 would be, the date on which the patient's balance went from 0 to > 0 and subsequently went to 0 again.
This would be on 1/23/06, in the data above.
x=2 would be, do the above calculation, but begin on the date calculated for x=1.  So going backward from 1/23/06, the date that the patient had a balance start that was paid off was on 1/15/06 -> (I would call this two billing cycles ago)
x=3 would be, do the above calculation, but begin on the date calculated for x=2.  So going backward from 1/15/06, the date that the patient had a balance start that was paid off was 1/12/06. -> (I would call this three billing cycles ago)

In the data at the top, I separated the previous billing cycles by year, for simplicity and clarity.  So on 1/1/03, 1/1/02, 1/1/01 and 1/1/00, a "cycle of having a balance" begins.

thanks,
gsgi
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial