Notifications
Clear all
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
give me a minute, i can write it up really quick
Posted : July 6, 2012 2:54 am
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
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
that is fine but you would need to have last name using in as well
Posted : July 6, 2012 4:42 am
How and from where could you access sql directly?
Posted : October 16, 2012 7:49 am
[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
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
[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
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
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
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
[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
