Notifications
Clear all
Topic starter
Hi, I am looking to see if there is a way that you can pull up only NEW patients in a time frame? If so, could you please provide me with the table.field name for the crystal reporting. Thanks!
Posted : September 27, 2012 1:21 am
you could probably look at DOCUMENT.VISDOCID. should be 1 for first document in a patients chart.
Posted : September 27, 2012 1:28 am
Topic starter
I was hoping for it to be in date format? How will that work with DOCUMENT.VISDOCID?
Posted : September 27, 2012 1:51 am
Topic starter
When I added DOCUMENT.VISDOC to the report, it took so long for it to go through the database & it locked up my computer. Looking for a better & quicker solution.
Posted : September 27, 2012 2:13 am
SELECT *
FROM PERSON
WHERE DB_CREATE_DATE >={ts '2012-09-22 00:00:00'} AND DB_CREATE_DATE<{ts '2012-09-28 00:00:00'}
this is the SQL query from Crystal
Posted : September 27, 2012 3:56 am
Topic starter
I had thought about using that query but I wasnt sure it was that accurate enough? Where did you find the query? Do you have the direct link? Does it specifically say that PERSON.DB_CREATE_DATE refers to the date that the patient was entered in the system as a new patient? Thanks!
Posted : September 27, 2012 4:13 am
I created a Crystal report looking for person info with select criteria of
DB_CREATE_DATE in [CurrentDate - 5 to CurrentDate]
Direct link?
From the data dictionary:
DB_CREATE_DATE is the date the person was first added to the table. DB_UPDATE_DATE is the date the person's info was last modified.
Posted : September 27, 2012 4:21 am
Topic starter
Thanks for replying. I created a report to list all the patients & their create dates & not all patients were showing a create date. Some had dates & some were blank? Was your report the same way?
Posted : September 27, 2012 4:52 am
I've been trying to get the query to run in SQLPlus and find that all DB_CREATE_DATE fields are null. So I'm not sure how the report found 18 pages of patients for the last 5 days. Grouping by DB_CREATE_DATE shows about 50,000 pages of 166,000 pages having no date in the field. I know we import from a scheduling system, so there may not be a date in this field for those. In a nutshell, I'm stumped. Sorry for the confusion.
Posted : September 27, 2012 5:22 am
Topic starter
Thanks for trying. If you happen to find a solution this, please do not hesitate to contact me. Thanks!
Posted : September 27, 2012 5:52 am
Topic starter
Hi everyone, I am still looking & in need for more assistance on pulling NEW patients for my crystal report. If you can be at any help, let me know. Thank you!
Posted : September 27, 2012 10:21 pm
wsc27 said:
Hi everyone, I am still looking & in need for more assistance on pulling NEW patients for my crystal report. If you can be at any help, let me know. Thank you!
I already posted this to you but someone removed it, this is what you are looking for, just change the dates as needed and the * to whatever fields you need.
SELECT *
FROM PatientProfile
WHERE Created BETWEEN '9/1/2012' AND '9/28/2012'
Posted : September 27, 2012 11:47 pm
The above was for CPS, for EMR, you can use this:::
SELECT dbo.PERSON.*
FROM dbo.PERSON INNER JOIN
dbo.[DOCUMENT] ON dbo.PERSON.PID = dbo.[DOCUMENT].PID
WHERE (dbo.[DOCUMENT].VISDOCID = '1') AND [DOCUMENT].DB_CREATE_DATE BETWEEN '9/1/2012' AND '9/28/2012'
Just change the dates and the * to fields you want.
Posted : September 28, 2012 12:33 am
You'll miss any new patients where the first document was discarded with the above select. Here's a report that will check the first 3 VISDOCIDs.
Posted : September 28, 2012 1:34 am
If that is the case use this instead, more accurate::::
SELECT dbo.PERSON.*
FROM dbo.PERSON INNER JOIN
dbo.[DOCUMENT] ON dbo.PERSON.PID = dbo.[DOCUMENT].PID
WHERE (dbo.[DOCUMENT].VISDOCID IN (SELECT MIN(VISDOCID) FROM DOCUMENT WHERE PID = dbo.PERSON.PID)) AND [DOCUMENT].DB_CREATE_DATE BETWEEN '9/1/2012' AND '9/28/2012'
Posted : September 28, 2012 1:47 am
Page 1 / 2
Next
