Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Schedule views SQL

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

4 Posts

4 Users

0 Reactions

57 Views

RSS

rfisher

(@rfisher)

Posts: 126

Estimable Member

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

jfitzmd

(@jfitzmd)

Posts: 419

Reputable Member

Glad to help you, contact me directly, [email protected].

Posted : August 4, 2016 8:24 am

Jonathan

(@jonschultziddc)

Posts: 26

Eminent Member

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

dmontgomery

(@dmontgomery)

Posts: 59

Trusted Member

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

Share:

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Powered by wpForo Powered by wpForo version 2.4.0