Hi All,
It seems like I always come with the big ones, I have been working on this for what seems to be forever. I am working in Crystal 9 attaching to ODBC. The Code below is a code that is actually a module in an access database application that "buckets" patients into "6" buckets or Clinical business units based on doctor service, patient service, floor, now this module works in acess now I am trying to recreate it in Crystal as a formula.
Below the access formula I will attach what I have done...my numbers are close but I seem to be off in cancer and in medicine CBU...any suggestions would be appreciated.
Here is the Crystal one I have been working on.
It seems like I always come with the big ones, I have been working on this for what seems to be forever. I am working in Crystal 9 attaching to ODBC. The Code below is a code that is actually a module in an access database application that "buckets" patients into "6" buckets or Clinical business units based on doctor service, patient service, floor, now this module works in acess now I am trying to recreate it in Crystal as a formula.
Below the access formula I will attach what I have done...my numbers are close but I seem to be off in cancer and in medicine CBU...any suggestions would be appreciated.
Code:
Function CBU(DOCSERV, Age, DG_FLOOR, DG_ROOM, PATSERV, CTU, DISCHDATE)
DOCSERV = Trim(DOCSERV)
DG_FLOOR = Trim(DG_FLOOR)
DG_ROOM = Trim(DG_ROOM)
PATSERV = Left(PATSERV, 2)
CTU = Trim(CTU)
DISCHDATE = Trim(DISCHDATE)
'Doctor Service is used to allocated patients into CBUs'
Select Case DOCSERV
Case "00001": CBU = "Medicine" 'Family/General Practioner
Case "00003": CBU = "Medicine" 'Emergency Medicine
Case "00010": CBU = "Medicine" 'Internal Medicine
Case "00011": CBU = "Medicine" 'Clinical Immuno/Allergy
Case "00012": CBU = "Surgery" 'Cardiology
Case "00014": CBU = "Medicine" 'Endocrinology/Metabolism
Case "00015": CBU = "Medicine" 'Gastroenterology
Case "00016": CBU = "Medicine" 'Nephrology
Case "00017": CBU = "Diagnostics" 'Neurology
Case "00018": CBU = "Medicine" 'Respirology
Case "00019": CBU = "Medicine" 'Rheumatology
Case "00020": CBU = "Women/Children" 'Pediatrics
Case "00022": CBU = "Women/Children" 'Pediatric Cardiology
Case "00024": CBU = "Women/Children" 'Pediatric Endocrinology
Case "00026": CBU = "Women/Children" 'Pediatric Nephrology
Case "00028": CBU = "Women/Children" 'Pediatric Respirology
Case "00030": CBU = "Surgery" 'General Surgery
Case "00031": CBU = "Surgery" 'Cardiac Surgery
Case "00032": CBU = "Diagnostics" 'Neurosurgery
Case "00034": CBU = "Surgery" 'Orthopedic Surgery
Case "00035": CBU = "Surgery" 'Plastic Surgery
Case "00036": CBU = "Surgery" 'Thoracic Surgery
Case "00037": CBU = "Surgery" 'Vascular Surgery
Case "00039": CBU = "Surgery" 'Urology
Case "00050": CBU = "Women/Children" 'Obstetrics and Gynecology
Case "00056": CBU = "Medicine" 'Clinical Pharmacology
Case "00057": CBU = "Medicine" 'Anesthesia
Case "00060": CBU = "Surgery" 'Otolaryngology
Case "00062": CBU = "Surgery" 'Ophthalmology
Case "00064": CBU = "Mental Health" 'Psychiatry
Case "00066": CBU = "Medicine" 'Hematology
Case "00067": CBU = "Women/Children" 'Pediatric Hematology
Case "00072": CBU = "Medicine" 'Geriatric Medicine
Case "00074": CBU = "Cancer" 'Medical Oncology
Case "00075": CBU = "Cancer" 'Radiation Oncology
Case "00096": CBU = "Medicine" 'Infectious Diseases
Case "01002": CBU = "Surgery" 'Dental Surgeon
Case "11004": CBU = "Women/Children" 'Midwife
End Select
'If the CBU field has been valued, run the rest of the module. If not, skip to end. This means we have a "New" doctor service and must define where it goes.
If (IsNull(CBU) = False) Then
'Allocates patients under 18 years of age who were discharged from a paediatric room to Women/Children'
If Age < 18 Then
If (DISCHDATE <= "2005/5/26") Then
If (((DG_FLOOR Like "W-7*") And ((DG_ROOM Like "71*") Or (DG_ROOM = "W7E1"))) Or (DG_FLOOR Like "W-PC*")) Then
CBU = "Women/Children"
End If
End If
If (DISCHDATE > "2005/5/26") Then
If ((DG_FLOOR Like "VD-7*") Or (DG_FLOOR Like "V-PC*")) Then
CBU = "Women/Children"
End If
End If
End If
'Allocates OBS Delivered, OBS Antepartum, OBS Aborted, and Newborn to Women/Children'
If PATSERV = "51" Or PATSERV = "52" Or PATSERV = "53" Or PATSERV = "54" Then
CBU = "Women/Children"
End If
'Places Malignant Hematology (00066), neuro oncology (00017 & 00032)and gyne oncology (00050) patients into the Cancer CBU'
If (DOCSERV = "00066" Or DOCSERV = "00017" Or DOCSERV = "00032" Or DOCSERV = "00050") Then
If (DISCHDATE <= "2005/5/26") Then
If ((DG_FLOOR Like "W-7*") And (DG_ROOM Like "72*")) Then
CBU = "Cancer"
End If
End If
If (DISCHDATE > "2005/5/26") Then
If (DG_ROOM Like "VC-7*") Then
CBU = "Cancer"
End If
End If
End If
'Allocate transplant patients
If PATSERV Like "37*" Then
'Keep Gastro, Nephrology & Hematology transplant patients in Medicine. Assign all others initially to Surgery.
If (DOCSERV <> "00015" And DOCSERV <> "00016" And DOCSERV <> "00066") Then
CBU = "Surgery"
End If
'Transplant exceptions
'Places any bone marrow transplants into Cancer
'I don't think we need this section if we can determine that all cancer transplants would have docserv = 00066
If (DISCHDATE <= "2005/5/26") Then
If ((DG_FLOOR Like "W-7*") And (DG_ROOM Like "72*")) Then
CBU = "Cancer"
End If
End If
If (DISCHDATE > "2005/5/26") Then
If (DG_ROOM Like "VC-7*") Then
CBU = "Cancer"
End If
End If
'Allocates paediatric transplant patients to Women/Children instead of Surgery
'NOTE: I DON"T THINK WE NEED THIS. PATSERV FOR PAED TRANSPLANT IS 47 (37 is ADULT TRANSPLANT)
If (DOCSERV = "00020" Or DOCSERV = "00026" Or DOCSERV = "00025") Then
CBU = "Women/Children"
End If
End If
'Allocates trauma patients to the Surgery CBU'
If ((DOCSERV = "00010") And (PATSERV = "38")) Then
CBU = "Surgery"
End If
'Allocates DOCSERV 00018 patients at South Street without CTU to Surgery'
If (DOCSERV = "00018") Then
If (DG_FLOOR = "S-S4") Then
If (DISCHDATE <= "2005/6/12") Then
If (IsNull(CTU) = True) Then
CBU = "Surgery"
End If
End If
End If
End If
'Don't allocate a CBU if the docserv hasn't been assigned yet
Else
CBU = Null
End If
End FunctionHere is the Crystal one I have been working on.
Code:
stringVar retVal := "";
select TRIM({CHDC.DOCSERV})
Case "00001": retVal := "Medicine" //'Family/General Practioner
Case "00003": retVal := "Medicine" //'Emergency Medicine
Case "00010": retVal := "Medicine" //'Internal Medicine
Case "00011": retVal := "Medicine" //'Clinical Immuno/Allergy
Case "00012": retVal := "Surgery" //'Cardiology
Case "00014": retVal := "Medicine" //'Endocrinology/Metabolism
Case "00015": retVal := "Medicine" //'Gastroenterology
Case "00016": retVal := "Medicine" //'Nephrology
Case "00017": retVal := "Diagnostics" //'Neurology
Case "00018": retVal := "Medicine" //'Respirology
Case "00019": retVal := "Medicine" //'Rheumatology
Case "00020": retVal := "Women/Children" //'Pediatrics
Case "00022": retVal := "Women/Children" //'Pediatric Cardiology
Case "00024": retVal := "Women/Children" //'Pediatric Endocrinology
Case "00026": retVal := "Women/Children" //'Pediatric Nephrology
Case "00028": retVal := "Women/Children" //'Pediatric Respirology
Case "00030": retVal := "Surgery" //'General Surgery
Case "00031": retVal := "Surgery" //'Cardiac Surgery
Case "00032": retVal := "Diagnostics" //'Neurosurgery
Case "00034": retVal := "Surgery" //'Orthopedic Surgery
Case "00035": retVal := "Surgery" //'Plastic Surgery
Case "00036": retVal := "Surgery" //'Thoracic Surgery
Case "00037": retVal := "Surgery" //'Vascular Surgery
Case "00039": retVal := "Surgery" //'Urology
Case "00050": retVal := "Women/Children" //'Obstetrics and Gynecology
Case "00056": retVal := "Medicine" //'Clinical Pharmacology
Case "00057": retVal := "Medicine" //'Anesthesia
Case "00060": retVal := "Surgery" //'Otolaryngology
Case "00062": retVal := "Surgery" //'Ophthalmology
Case "00064": retVal := "Mental Health" //'Psychiatry
Case "00066": retVal := "Medicine" //'Hematology
Case "00067": retVal := "Women/Children" //'Pediatric Hematology
Case "00072": retVal := "Medicine" //'Geriatric Medicine
Case "00074": retVal := "Cancer" //'Medical Oncology
Case "00075": retVal := "Cancer" //'Radiation Oncology
Case "00096": retVal := "Medicine" //'Infectious Diseases
Case "01002": retVal := "Surgery" //'Dental Surgeon
Case "11004": retVal := "Women/Children" //'Midwife)
;
If {ABSTRACT.ADMIT}="S" then "Women/Children" ;
//'Allocates patients under 18 years of age who were discharged from a paediatric room to Women/Children'
If stringVar retVal <> "Women/Children" then
If {ABSTRACT.AGE}< 18 then
If (({ABSTRACT.DISDATE}) <= DateTime(2005,5,26,00,00,00)) then
If (Trim({ABSTRACT.DG_FLOOR}) startswith "W-7"and Trim({ABSTRACT.DG_ROOM}) startswith "71"
Or Trim({ABSTRACT.DG_ROOM}) = "W7E1"
Or Trim({ABSTRACT.DG_FLOOR}) startswith "W-PC")Then
retval := "Women/Children";
If stringVar retVal <> "Women/Children" then
If {ABSTRACT.AGE}< 18 then
If ({ABSTRACT.DISDATE} > DateTime(2005,5,26,00,00,00)) Then
If ( {ABSTRACT.DG_FLOOR} startswith "VD-7" Or {ABSTRACT.DG_FLOOR}
startswith "V-PC" ) Then
retval := "Women/Children";
//'Allocates OBS Delivered, OBS Antepartum, OBS Aborted, and Newborn to Women/Children'
If stringVar retVal <> "Women/Children" then
If (trim({ABSTRACT.PATSERV})= "51." Or trim({ABSTRACT.PATSERV}) = "52."
Or trim({ABSTRACT.PATSERV}) = "53." Or trim({ABSTRACT.PATSERV})= "54.") Then
retval :="Women/Children";
//'Places Malignant Hematology (00066), neuro oncology (00017 & 00032)and gyne oncology (00050) patients into the Cancer CBU'
If stringVar retVal <> "Cancer" then
If (trim({CHDC.DOCSERV})= "00066" or trim({CHDC.DOCSERV})= "00017"
Or trim({CHDC.DOCSERV}) = "00032" Or trim({CHDC.DOCSERV})= "00050" )Then
If ({ABSTRACT.DISDATE} <= DateTime(2005,5,26,00,00,00)) Then
If (trim({ABSTRACT.DG_FLOOR}) startswith "W-7"
And trim({ABSTRACT.DG_ROOM})startswith "72" )Then
retval :="Cancer";
//If stringVar retVal <> "Cancer" then
If (trim({CHDC.DOCSERV})= "00066" or trim({CHDC.DOCSERV})= "00017"
Or trim({CHDC.DOCSERV}) = "00032" Or trim({CHDC.DOCSERV})= "00050") Then
If ({ABSTRACT.DISDATE}> DateTime(2005,5,26,00,00,00)) Then
If ({ABSTRACT.DG_ROOM} startswith "VC-7") Then
retval := "Cancer"
;
//'Allocate transplant patients
If {ABSTRACT.PATSERV} startswith "37." Then
//'Keep Gastro, Nephrology & Hematology transplant patients in Medicine. Assign all others initially to Surgery.
If (({CHDC.DOCSERV}) <> "00015" And ({CHDC.DOCSERV}) <> "00016"
And ({CHDC.DOCSERV})<> "00066") Then
retval :="Surgery"
;
//'Transplant exceptions
//'Places any bone marrow transplants into Cancer
//'I don't think we need this section if we can determine that all cancer transplants would have docserv = 00066
If ({ABSTRACT.DISDATE}<= DateTime(2005,5,26,00,00,00)) Then
If (({ABSTRACT.DG_FLOOR}startswith "W-7") And ({ABSTRACT.DG_ROOM} startswith "72")) Then
retval := "Cancer"
;
If ({ABSTRACT.DISDATE} > DateTime(2005,5,26,00,00,00)) Then
If ({ABSTRACT.DG_ROOM} startswith "VC-7") Then
retval :="Cancer"
;
//'Allocates paediatric transplant patients to Women/Children instead of Surgery
//'NOTE: I DON"T THINK WE NEED THIS. PATSERV FOR PAED TRANSPLANT IS 47 (37 is ADULT TRANSPLANT)
If ({CHDC.DOCSERV} = "00020" Or{CHDC.DOCSERV}= "00026" Or{CHDC.DOCSERV} = "00025") Then
retval :="Women/Children"
;
//'Allocates trauma patients to the Surgery CBU'
If (trim({CHDC.DOCSERV}) = "00010" And trim({PATSERV.PSNO})= "38." )Then
retval :="Surgery"
;
//'Allocates DOCSERV 00018 patients at South Street without CTU to Surgery'
If ({CHDC.DOCSERV} = "00018") Then
If (({ABSTRACT.DG_FLOOR})= "S-S4") Then
If ({ABSTRACT.DISDATE}<= DateTime(2005,5,26,00,00,00)) Then
If (isnull({ABSTRACT.CF13}) OR TRIM({ABSTRACT.CF13}) = "") then
retval := "Surgery";
//'Don't allocate a CBU if the docserv hasn't been assigned yet
If stringVar retVal <> "Women/Children"
Or stringVar retVal <>"Mental Health" or stringVar retVal <>"Surgery"
or stringVar retVal <>"Cancer" or stringVar retVal <>"Diagnostics"
or stringVar retVal <>"Medicine" then "No CBU has been Assigned";
retVal;