Notifications
Clear all
Topic starter
I need to find patients who had an office visit within a date range. I have used formulas earlier. But now, I would like to know how to do it using pure SQL. Can anyone please help? Thank you.
Posted : December 20, 2012 5:38 am
The centricity database has a function that do that conversionts the id to a date.
dbo.Convert_ID_to_date([DOCUMENT].CLINICALDATE)
You could use a select statement like
Select * from document where dbo.Convert_ID_to_date([DOCUMENT].CLINICALDATE) between '1/1/2013' and '1/31/2013'
John Kingery
Outsde IN - @outsidein.org" rel="nofollow" target="_blank">johnk@outsidein.org
Posted : February 14, 2013 8:04 am
Topic starter
jmkoi, Thank you so much. This will be very useful in my future reports. I have been trying to do this for a long time.
I had to change it as follows:
dbo.Convert_ID_to_date(DOCUMENT.CLINICALDATE) between to_date ('01-01-2011', 'mm-dd-yyyy') and to_date ('12-31-2012', 'mm-dd-yyyy')
Thanks again.
Posted : February 20, 2013 2:14 am
Topic starter
John, forgot to ask in the previous post. Where can I find the other conversion functions?
Posted : February 20, 2013 2:46 am
baj said:
John, forgot to ask in the previous post. Where can I find the other conversion functions?
Bar,
You can find them as functions in the centricity database. We use the MS-SQL version of CPS. It looks like you are using the Oracle version and I believe you will find those functions in the same area.
Posted : February 21, 2013 3:24 am
Topic starter
Found it under Stored Procedures. Thanks a lot.
I am using the Oracle version. That's why I had to change the date format in the SQL you sent, I believe.
Posted : February 21, 2013 3:39 am
