Category Archives: SQL

Scheduling lag (or, “Fun With Recursive CTEs!”)

Hey, who wants to learn about recursive common table expressions? Today we’re going to look at a scenario where one of our practices wants to know how far out they’re scheduling appointments. For example, the user wants to know how many appointments have a lag of at least thirty days between being scheduled, and taking place. A recursive common table expression can get us there quickly and easily.
Continue reading

Adding SFTP functionality to your SSIS packages

Hi folks! This one’s not at all Centricity-centric, but it involves a task you may sometimes need to perform as part of your work with patient data. Let’s say your organization is part of an ACO, and that ACO requires regular uploads of data. In most cases, they’re going to want you to upload that data via SFTP. If the data is due monthly, you could run your script on the first of the month, export the data, then upload it manually via the FTP application of your choice. But what if you’re on vacation? What if you forget? What if the data needs to be uploaded daily? Are you really going to do this manual upload every single day? You could, but why would you want to? The more efficient method is to automate it via an SSIS package.
Continue reading

The CMS code functions

Hello friends! Wow, it has been way too long since my last post. Things have been absurdly busy, as I’m guessing they have for you. I’d like to be more active on this blog in 2017, so let’s start now!

I get a lot of requests to pull data pertaining to CMS measures, especially for purposes of tracking our progress against CQR. That system is not the least bit user-friendly, so it’s nice to be able to bring up a report that shows how we’re doing organization-wide on a given measure. Continue reading

A quick list-to-array conversion trick

Somebody sends you an Excel spreadsheet with a bunch of OBS terms on it. Could be patient PIds, could be anything – it’s a big list. You want to use that list in a query, and there could be dozens or hundreds of them. Options are to set up a data connection to the spreadsheet, or paste the list into your query and start adding single quotes and commas. Or, you can copy this little bit of code and put it in your tricks file: Continue reading

Finding duplicate SSNs (or other things)

I attended the Intermediate PM Reports class in Boston this week, and met some great people (learned a lot, too!). Everyone came with their own list of problems they wanted to solve. One of my classmates wanted to identify duplicate SSNs in the patient data. While she left with a working solution, I think we could take it one more step and learn a bit more about SQL in the process. Continue reading

Turnaround Times

Our practice managers wanted to be able to track the time from when a patient was checked in, to when they were roomed, to when they were seen by the provider, to when they were checked out. We spent some time researching how this could be done, and were not able to come up with a reliable way to identify rooming or when the doctor walked in.

We considered using the time vitals were taken as “roomed,” but the managers (who know their staff’s workflows far better than I) didn’t feel that would be accurate enough. And none of our providers start documenting as soon as they walk in the room, so we decided we had to abandon those two measures for now. But we can definitely track check-in to check-out. Continue reading