Hi,
I'm using 2008 and am struggling with some calculations and hoping someone would be kind enough to help.
A record (person) can have more than one categorisations of their referral, with a date and category code being added to their record. I need to count days the referral was UC (uncategorised). A scenario can be:
Patient 1 8/11/2013 UC
12/11/2013 AI
20/11/2013 UC
25/11/2013 2
So the result is 9 days UC. I also have to take exclude weekends in the count. I have grouped on Patient, have a reset variable in the group header (suppressed) and SetTotal Days in the group footer:
whileprintingrecords;
Global numberVar TotalUCDays;
TotalUCDays:= TotalUCDays + (if previous ({OPDReferrals.Urno})={OPDReferrals.Urno} and
Previous ({OPDReferral__CatHistory.ReferralCategoryCode})="UC" then
(DateDiff ('d',PREVIOUS({OPDReferral__CatHistory.CategorisationDate}),{OPDReferral__CatHistory.CategorisationDate}) -
DateDiff ("ww", PREVIOUS({OPDReferral__CatHistory.CategorisationDate}), {OPDReferral__CatHistory.CategorisationDate}, crSaturday) -
DateDiff ("ww", PREVIOUS({OPDReferral__CatHistory.CategorisationDate}), {OPDReferral__CatHistory.CategorisationDate}, crSunday)
)
else 0
)
It appears to be working OK - please let me know if there is a more succinct way of doing. I now need to summarise the resulting UC days but am going around in circles. I want to summarise by referral clinic and UC days <4 days and UC days >4, so this will show a trend from month to month (in select criteria). Tried a cross-tab and running totals, but the SetTotalDays formula doesn't show - I guess because cannot summarise a summary?
Any light would be very much appreciated.
Thanks
B