When you start using Gooroo Planner, the first task is a technical one – writing database queries to extract anonymised data from your local IT system, ready for upload into Gooroo Planner.

We like to come on-site and do this with you, so that it’s right first time. It usually takes less than a day for a good analyst to write decent queries and generate the first cut of modelling. Which is pretty fast, when you consider this is a detailed hospital-wide capacity plan covering hundreds of clinical services.

But we thought we could make it even faster.

So we’ve put together some standard SQL queries that cover most of the things you’re likely to need. We’ve cross-checked them against the SQL code that talented information analysts across the UK are already using to populate Gooroo Planner (with thanks to all of them for contributing). And we’ve published them on our website for you to use.

Every NHS IT system is structured differently, so you will still need to go through the queries line by line, customising them to suit your local data. But they should take care of enough of the main requirements to get you off to a flying start, and even those of you who are well settled-in with Gooroo Planner may find some of the code useful for enhancing your own queries.

Ready to automate

The queries are also built with automation in mind, just like Gooroo Planner itself.

For instance the first query sets up common date ranges for all the other queries, and even generates a date settings table ready for fully-automatic unattended running using the Planner API.

It is also worth creating a script or stored procedure that goes the last mile, by running all the queries in sequence and generating the CSV files. Not only will that save you from effort and error whenever you refresh the data, it means you will be ready to move to full automation of your regular planning requirements when the time is right.

Further resources

The SQL code (including a Read Me file with further information) and the Gooroo Planner documentation are available from https://gooroo.co.uk/publications/

An overview of the data preparation process is at https://blog.gooroo.co.uk/2017/10/loading-the-data-into-gooroo-planner/

Support is available from [email protected] and is inclusive with Gooroo licences.