Notifications
Clear all
Topic starter
SELECT * FROM MedLists WHERE TableName = 'NamedSchedule'
I was able to find the schedule views in the Medlists table. Can anybody point me in the right direction on which table to join so I can see which schedules are actually assigned to these?
Posted : August 4, 2016 8:15 am
The following query will list objects containing "NamedSchedule":
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%NamedSchedule%'
You can then work your way through the objects (for example, Script Stored Procedure as > Create to > New Query Window)
One of the promising items is sGetNamedSchedule:
CREATE PROCEDURE [dbo].[sGetNamedSchedule] @pNamedScheduleId int AS
SELECT DoctorFacilityId, ListName, df.Type
FROM DoctorFacility df
JOIN MedLists ml ON ml.OtherLong = df.DoctorFacilityId
WHERE (TableName = 'NamedScheduleResource' OR TableName = 'NamedScheduleFacility')
AND ml.JoinId = @pNamedScheduleId
ORDER BY ListOrder, ListName
GO
So it looks like for each NamedSchedule record in MedLists there are one or more NamedScheduleResource or NamedScheduleFacility records in MedLists.
sGetNamedSchedule will return the list of resources and facilities for the named schedule.
Posted : August 4, 2016 4:57 pm
Something along these lines should retrieve the list of Facilities and Resources that your NamedSchedules are pointing to. Hopefully this points you in the right direction.
Select mlNS.Description as 'Named Schedule',
dfF.ListName as 'Facility Name',
dfF.DotId as 'Facility DOT ID',
dfR.ListName as 'Resource Name',
dfR.DotId as 'Resource DOT ID'
From MedLists mlNS
LEFT JOIN MedLists mlNSF ON mlNS.MedListsId = mlNSF.JoinId AND mlNSF.TableName = 'NamedScheduleFacility'
LEFT JOIN DoctorFacility dfF ON mlNSF.OtherLong = dfF.DoctorFacilityId
LEFT JOIN MedLists mlNSR ON mlNS.MedListsId = mlNSR.JoinId AND mlNSR.TableName = 'NamedScheduleResource'
LEFT JOIN DoctorFacility dfR ON mlNSR.OtherLong = dfR.DoctorFacilityId
Where mlNS.TableName = 'NamedSchedule'
Posted : August 5, 2016 4:21 am
