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

Patient next appt date in sql

CPS, C-EMR, PM

Last Post by Anonymous 1 second ago

13 Posts

4 Users

0 Reactions

72 Views

RSS

ErnieT

(@erniet)

Posts: 479

Honorable Member

Topic starter

Has anyone written a sql script to pull the patients next appointment date, and only the next appointment?

Wanted to ask before I went out and wrote it myself.

Thanks

Posted : July 5, 2012 6:02 am

acantu

(@acantu)

Posts: 722

Prominent Member

give me a minute, i can write it up really quick

Posted : July 6, 2012 2:54 am

acantu

(@acantu)

Posts: 722

Prominent Member

Here you go, just replace FIRST and LAST with the patients first name and last name and you should have the next appointment for that patient.

– Code Below

SELECT   TOP 1     pp.PId, apmt.AppointmentsId, apmt.FacilityId, apmt.ApptKind, apmt.OwnerId, apmt.EmrApptStart AS ApptStart, apmt.Duration, apmt.Status, apmt.Canceled,
                         apmt.PatientCheckIn, apmt.Notes, apmt.ApptStatusMId, apmt.DoctorId, apmt.ResourceId, apmt.Type, apmt.PatientVisitId, apmt.HideNewVisit, apmt.ApptTypeId,
                         apmt.ApptSetId, apmt.ApptChainId, apmt.ExternalApptId, apmt.DocCreate, apmt.DocId, apmt.ExternalVisitId, apmt.Created, apmt.CreatedBy, apmt.LastModified,
                         apmt.LastModifiedBy
FROM            dbo.Appointments AS apmt INNER JOIN
                         dbo.PatientProfile AS pp ON apmt.OwnerId = pp.PatientProfileId
WHERE        (apmt.ApptKind = 1) AND PID IN (
SELECT PID
FROM dbo.PatientProfile
WHERE First = 'FIRST' and Last = 'LAST') AND ApptStart > GETDATE()
ORDER BY ApptStart ASC

Posted : July 6, 2012 3:01 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

acantu, I don't think that's flexible enough — works for one person, but not more than one. This is probably better.

select PatientId, NextAppt from (
    select pp.PatientId, ap.ApptStart
            , min(ap.ApptStart) over (partition by ap.ownerId) as NextAppt
        from Appointments ap
            join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
        where pp.First in ('Thing 1', 'Thing 2', 'Cat', 'Hat')
            and pp.Last = 'Seuss'
            and ap.ApptKind = 1
            and ap.ApptStart > getdate()
        ) ApptList 
    where ApptStart = NextAppt

Posted : July 6, 2012 3:54 am

acantu

(@acantu)

Posts: 722

Prominent Member

that is fine but you would need to have last name using in as well

Posted : July 6, 2012 4:42 am

[email protected]

(@talontlcdoctors-com)

Posts: 86

Estimable Member

How and from where could you access sql directly?

Posted : October 16, 2012 7:49 am

acantu

(@acantu)

Posts: 722

Prominent Member

[email protected] said:

How and from where could you access sql directly?

Do you have your own servers on site? or is someone else hosting? If you have your own, just use SQL Management tool to do what you need to do, if not, you have to ask the company that is hosting the database to give you access...

Posted : October 16, 2012 8:04 am

[email protected]

(@talontlcdoctors-com)

Posts: 86

Estimable Member

Thanks. We have our own servers. I'm not familiar with the SQL Management tool, though. If it comes with SQL, then it's probably already on the server.

Is it possible to make a call to SQL directly from centricity, say in a form letter or update? If not, when would you use the management tool and why?

Posted : October 16, 2012 8:15 am

acantu

(@acantu)

Posts: 722

Prominent Member

[email protected] said:

Thanks. We have our own servers. I'm not familiar with the SQL Management tool, though. If it comes with SQL, then it's probably already on the server.

Is it possible to make a call to SQL directly from centricity, say in a form letter or update? If not, when would you use the management tool and why?

You can make your own forms and stuff, when using tools like crystal reports. But I use SQL Management to make my own custom reports without crystal, along with addon applications... Some people like using Excel to pull data as well, Excel has a SQL Management Data retrieval tool as well. I do not recommend you using SQL Management tool if you do not know how to use it, you can destroy your database if you do something incorrectly.

Posted : October 16, 2012 8:19 am

[email protected]

(@talontlcdoctors-com)

Posts: 86

Estimable Member

Acantu,

Thanks for the info and recommendation.

I am very comfortable with SQL, just not too familiar with SQL Management. I haven't ponied up to purchase Crystal Reports yet, and I would like to try my hand an running some queries to see if I'm in the ballpark. Where would you recommend I begin? Also, where do I go to learn about the db and tables to get at the data I am hunting for?

Thanks so much for any help you can offer.

Posted : March 10, 2013 8:00 am

acantu

(@acantu)

Posts: 722

Prominent Member

http://centricitypractice.gehe.....hcare.com/ then on the left, click on the version of CPS you are using, then click on Documentation and finally look at the Data Dictionary, you can down the HTML version or ZIP version. This will show you a lot about how the tables connect and a little bit about what each table and columns are.

http://msdn.microsoft.com/en-u.....90%29.aspx , here you can get some basics about how to write sql queries. You can use micorosft access, excel by adding a connection to your CPS database. Also, you can use SQL Management Studio, but if you use studio, make sure you only use the SELECT statement until you are comfortable and understand how to fully write scripts or create a new view and play with query scripts there.

If you need any further assistance, please let me know.

Posted : March 11, 2013 12:29 am

[email protected]

(@talontlcdoctors-com)

Posts: 86

Estimable Member

Thanks, acantu. That Data Dictionary is going to be a big help.

As for SQL, I obviously need a sandbox where I can try my hand at some SELECT statements. I do have SQL Management Studio, but I don't know where to start to run some queries. What portion or where in the studio do I go to query the tables?

I really appreciate your help!

Posted : March 11, 2013 2:04 am

acantu

(@acantu)

Posts: 722

Prominent Member

[email protected] said:

Thanks, acantu. That Data Dictionary is going to be a big help.

As for SQL, I obviously need a sandbox where I can try my hand at some SELECT statements. I do have SQL Management Studio, but I don't know where to start to run some queries. What portion or where in the studio do I go to query the tables?

I really appreciate your help!

Perfect, open your SQL Manager and connect to your database. Open the Databases tree [+] then open the tree for the exact database you want to make queries for [+] you should now see a folder that says Views, right click on the folder and select New View...

There you go, that is the easiest way to start testing. Hopefully you are using a test database though. Until you get comfortable enough to do it on a live database.

Posted : March 11, 2013 2:09 am

24 Forums
9,942 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