Crystal 2008
MS SQL DB
Given the following table structure:
Provider.Provider_Name (Group 1)
Prov_Spec.Line Specialty.Name
---------------------------------
Dr A
1 Internal Medicine
2 Hospitalist
Dr B
1 Hospitalist
2 Internal Medicine
Dr C
1 Internal Medicine
Dr D
1 Hospitalist
2 Pediatrics
3 Internal Medicine
Dr E
1 Hospitalist
I need to group physicians to a new type (let's call it ServiceLine). ServiceLine is dependent upon specialty, but if there are multiple specialties, it can get tricky. I'm not sure how to make groupings when I have to compare across results.
If they have Internal Medicine (Specialty_ID 2345) then they belong to ServiceLine="Primary Care" unless they have Hospitalist (Specialty_ID 6789) as a specialty, then they should be in ServiceLine="Inpatient" (and not in "Primary Care"), but if they also have Pediatrics (Specialty_ID 1111) they they should be in both "Inpatient" and "Women and Childrens" ServiceLines.
My result should look like:
Primary Care
Dr C
Inpatient
Dr A
Dr B
Dr D
Dr E
Women and Childrens
Dr D
MS SQL DB
Given the following table structure:
Provider.Provider_Name (Group 1)
Prov_Spec.Line Specialty.Name
---------------------------------
Dr A
1 Internal Medicine
2 Hospitalist
Dr B
1 Hospitalist
2 Internal Medicine
Dr C
1 Internal Medicine
Dr D
1 Hospitalist
2 Pediatrics
3 Internal Medicine
Dr E
1 Hospitalist
I need to group physicians to a new type (let's call it ServiceLine). ServiceLine is dependent upon specialty, but if there are multiple specialties, it can get tricky. I'm not sure how to make groupings when I have to compare across results.
If they have Internal Medicine (Specialty_ID 2345) then they belong to ServiceLine="Primary Care" unless they have Hospitalist (Specialty_ID 6789) as a specialty, then they should be in ServiceLine="Inpatient" (and not in "Primary Care"), but if they also have Pediatrics (Specialty_ID 1111) they they should be in both "Inpatient" and "Women and Childrens" ServiceLines.
My result should look like:
Primary Care
Dr C
Inpatient
Dr A
Dr B
Dr D
Dr E
Women and Childrens
Dr D