Notifications
Clear all
Topic starter
Hello,
I need help with a script that will show all the Active Appointment Types in GE CPS. We are on CPS 12.0.12. Thanks!
Posted : December 1, 2017 8:46 am
select * from ApptType where Inactive = 0 or Inactive is NULL
Posted : December 1, 2017 9:03 am
How difficult would it be to add the apptTypeAssignment to this script?
Linda
Posted : December 4, 2017 5:28 am
This would basically link the tables together, but you'd have to change the script to clean up the values for what you do and don't want to see. The code below would show repeating ApptType values for each ApptTypeAssignments, and I'm assuming that is what you would like to know.
select *
from ApptType AT
left join ApptTypeAssignments ATA
ON ATA.ApptTypeId = AT.ApptTypeId
where AT.Inactive = 0 or AT.Inactive is NULL
order by AT.ApptTypeId
Posted : December 4, 2017 5:42 am
Thanks, actually I was thinking more along the lines of which Resources that appt type is assigned to, preferably the name of the provider or resource along with the information that your original select pulled.
Posted : December 4, 2017 7:40 am
I believe that this is what you are looking for. This would tie the doctors and resources to the ApptTypeAssignments table.
I copied the ListName and OrgName columns to the front of the results just so they are more visible. Your final query should call out all specific columns from each table without using the asterisks like in the example code below. The example below shows all results from two out of the three involved tables.
select DF.OrgName, DF.ListName, AT.*, ATA.*
from ApptType AT
left join ApptTypeAssignments ATA
ON ATA.ApptTypeId = AT.ApptTypeId
left join DoctorFacility DF
ON DF.DoctorFacilityId = ATA.DoctorResourceId
where AT.Inactive = 0 or AT.Inactive is NULL
order by AT.ApptTypeId
Posted : December 4, 2017 8:14 am
Thanks! That looks great!
Linda
Posted : December 5, 2017 2:55 am
