gsgi
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
... 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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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...