Notifications
Clear all
You will be able to link like you do now, just to the view instead of the DOCUMENT table itself. The SELECT a.* will return all fields from the DOCUMENT table.
Posted : September 13, 2012 11:05 pm
Topic starter
I am going to try to do it as you suggested. Thank you very much, JJ.
Posted : September 13, 2012 11:18 pm
You'll want this to get only office visits:
SELECT a.*
FROM ml.DOCUMENT AS a
WHERE a.DOCTYPE = 1
AND a.CLINICALDATE = SELECT MAX(b.CLINICALDATE)
FROM ml.DOCUMENT AS b
WHERE b.PID = a.PID
AND b.DOCTYPE = 1
Posted : September 13, 2012 11:34 pm
You'll want to call the view something like LASTOFFICEVISITS.
Posted : September 13, 2012 11:36 pm
Topic starter
How do I create a view as you said? When you gave the SQL, I was going to do with Add Command. Could you please give the specific steps needed?
Posted : September 13, 2012 11:44 pm
I usually have our DBA create any views that I need for reporting...don't have direct access myself...so not sure of exact syntax or process.
Posted : September 13, 2012 11:48 pm
Topic starter
I also do everything from inside Crystal Reports. But I'll try to incorporate the code you gave inside the Add Command so I can filter only the last office visits. I feel it will work. Thank you again, JJ.
Posted : September 13, 2012 11:57 pm
I didn't know about the Add Command feature...might come in handy later.
Posted : September 14, 2012 12:07 am
Topic starter
JJ, That is a very useful feature I have come across in CR. I have used it before. What I usually do is create a report in CR like a normal report linking tables needed adding selection criteria,etc and then copy and paste the SQL into a new report, so I can use it to get dynamic (and sometimes cascading) parameters.
One eg. was when I wanted to pull appointments of patients at a particular location having certain procedures on a selected date (parameter) and select a provider(parameter -who has patients scheduled on that date) and get selected patient(parameter). This is a dynamic cascading parameter. ApptDate > Provider > Patient
I can help you(with whatever I know) if you need any help.
I would like to know if anyone knows if there is a way to create a view as JJordet said from inside CR and link other tables to the created view in a way that we link tables.
Posted : September 14, 2012 12:31 am
Topic starter
Hi JJ,
Sorry for the late reply. But just wanted to let you know I could not do it within Add Command. It was giving an error. I do not have access to create view. I am going to ask for it.
Regarding the report, since we needed the first 100 patients who were last seen, I ran the report for each day individually and gave the list.
Thank you once again for your help.
BAJ
Posted : September 17, 2012 5:26 am
Page 2 / 2
Prev
