| Author |
Message |
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
   Posted: 18 Mar 2010 at 6:18am |
Here is the formula:
Days between pmts:
datediff('d',date( {@SP Pymnt date}),date( {@Ins Pymnt date}))
The error I get now is "dates must be between year 1 and year 9999".
The sp payment formula is :
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}
The ins pymnt date formula is:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}
We are trying to calculate the number of days between the self pay payment and the insurance payment.
Thank you for your help!
|
 IP Logged |
|
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
Posted: 18 Mar 2010 at 6:41am |
We are trying to calculate the number of days between payments.
I made some changes to the formula...
Here is the formula:
datediff('d',date( {@SP Pymnt date}),date( {@Ins Pymnt date}))
SP Pymnt date formula:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}
Ins Pmymnt date formula:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}
All formulas check out with no errors. When I refresh the report I now get the error "dates must be between year 1 and year 9999".
Thanks for your help!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Mar 2010 at 8:43am |
1. what are some sample values from the {DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} field and what data type is is?
2. you are trying to compare one row to another row, not 2 dates frm ther same row correct?
|
|
IP Logged |
|
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
Posted: 18 Mar 2010 at 8:47am |
one sample is:
20090821
20090904
These values are in the details. The group for the report is Encounter Payment.Encounter Number
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Mar 2010 at 9:02am |
1. It appears that this is just a number field that you will need to convert into a date field (it thinks you are using the year of 20090821, not the date of 2009-08-21).
try this for that:
date(left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),4)+'/'+mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),5,2) + '/' + right(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),2))
2. You will need the NEXT function to evaluate from one row to the next.
Hard to tell exacly how to do that.
Post multiple rows (including the PlanCode) and how you want to calcualte it.
|
|
IP Logged |
|
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
Posted: 18 Mar 2010 at 9:22am |
See below for examples of output. We need to calculate the number of days between the SP payment and the Insurance payment. If there is an easier way to do this please let me know...I've tried multiple different formula's so I'm open to suggestions. I do not necessarily think we need the last four columns...they were added by someone else but I left them in just case we need them. Again...thank you for your help.
| |
|
|
|
|
|
Ins |
SP |
Ins |
SP |
| |
Encounter # |
Insurance Plan |
Payment Type |
Payment Amt |
Payment Date |
Pymnt |
Pymnt |
Pymnt Date |
Pymnt Date |
| Group #1 header |
XX0001234567 |
|
|
|
|
|
|
|
|
| Details |
XX0001234567 |
XXX.XX.XX |
RCP |
6,410.1 |
20090821 |
6,410.06 |
0.00 |
20090821 |
0 |
| Details |
XX0001234567 |
SP |
RCP |
1,318.6 |
20090904 |
0.00 |
1,318.57 |
0 |
20090904 |
| Group #1 footer |
XX0001234567 |
|
|
|
|
6,410.06 |
1,318.57 |
20090821 |
20090904 |
| |
XX0001234568 |
|
|
|
|
|
|
|
|
| |
XX0001234568 |
XXX.XX.XX |
RCP |
3,841.6 |
20090918 |
3,841.60 |
0.00 |
20090918 |
0 |
| |
XX0001234568 |
SP |
RCP |
770.1 |
20090925 |
0.00 |
770.14 |
0 |
20090925 |
| |
XX0001234568 |
|
|
|
|
3,841.60 |
770.14 |
20090918 |
20090925 |
| |
XX0001234569 |
|
|
|
|
|
|
|
|
| |
XX0001234569 |
XXX.XX.XX |
RCP |
15,341.2 |
20091023 |
15,341.19 |
0.00 |
20091023 |
0 |
| |
XX0001234569 |
SP |
RCP |
320.6 |
20091113 |
0.00 |
320.61 |
0 |
20091113 |
| |
XX0001234569 |
|
|
|
|
15,341.19 |
320.61 |
20091023 |
20091113 |
| |
XX0001234560 |
|
|
|
|
|
|
|
|
| |
XX0001234560 |
XXX.XX.XX |
RCP |
252.1 |
20100108 |
252.06 |
0.00 |
20100108 |
0 |
| |
XX0001234560 |
SP |
RCP |
529.6 |
20100115 |
0.00 |
529.62 |
0 |
20100115 |
| |
XX0001234560 |
|
|
|
|
252.06 |
529.62 |
20100108 |
20100115 |
|
|
IP Logged |
|
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
Posted: 18 Mar 2010 at 9:30am |
With this formula I get an error..."bad date format string".
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
date(left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),4)+'/'+mid(totext
({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),5,2) + '/' + right(totext
({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),2))
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Mar 2010 at 9:35am |
what about:
date(mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),5,2) + '/' + mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),7)+'/'+left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),4))
Where do you need to display the date difference at?
Edited by DBlank - 18 Mar 2010 at 9:36am
|
|
IP Logged |
|
benner
Newbie
Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
|
Posted: 18 Mar 2010 at 9:43am |
The formula worked! Thank you!
Was thinking we would put the days difference in the Group #1 footer.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Mar 2010 at 10:25am |
If you always only have 2 rows per group:
Use the aobe formula to convert the field to a date, I'll call it '@DateField' for my example. Now use a datediff function with a Maxi and Min values at group1 to get the value
Place in group footer1
|
|
IP Logged |
|
|
|
