Returning a single value from multiple values

[Maddye]

What I’m trying to do is to distil multiple involvement values into the most significant value based on the following criteria in order of preference:

1. A team that is open on a specific date - the involvement may be subsequently ended. If there is more than 1, take the involvement with the most recent start date.

2. For anyone that doesn’t have an open team on the specific date (1), take a team ended during the reporting period. If there is more than 1, take the team with the most recent end date, and then the team with the most recent start date.

3. For anyone not in (1) or (2), the logic of (1) but using the team of the person open on a specific date.

4. For anyone not in (1), (2) or (3), the logic of (2) but using the team of the person.

Initially, I did this as a single query returning all people and teams open during the reporting period, but that was over 50% #MULTIVALUE errors which I couldn’t clear. I then split it into 4 queries based on the 4 conditions, and got it down to 10% #MULTIVALUE errors. Again, I haven’t been able to clear them. It also adds significantly to the query time.

Ideally, I’d like to handle this as a single query avoiding duplicate rows wherever possible, but short of doing it as an insanely complex UNION query (I really wish there was a way to simplify UNIONs using the in list/not in list logic instead of using sub-queries), I don’t see a way round it. I could add involvements to the main query, but we run into the issue of users with no involvements during the date period and some of our records stretch back years.

We’re using BI 4.1, sp5 and I don’t have access to the universe. If anyone has any ideas, please help! I suspect it’s a matter of using FOREACH (or FORALL) and adding start and end dates into the formulas but I’ve tried that and it doesn’t seem to work very well.

[dtolley]

Can you provide sample data and the expected results. Also need definitions/illustrations of terms like open, ended, specific date – where does that come from and does it change. In some places you refer to teams and in others people – how are they related and which are you reporting on? Can a person be on multiple teams?

[Maddye]

I can’t provide sample data because of Information Governance issues.

Basically, what I am doing is extracting from an adult social care database, and I am extracting a list of service users (SUs) with services active during a time period defined by prompted start and end dates. These SUs are involved with social work teams - either directly if the user is not being actively worked with but is still in receipt of services, or indirectly via the team the social worker is attached to when the SU is being assessed or reviewed. The third option is historical involvements when the SU’s case is closed during the time period. SUs can be involved with multiple workers and/or teams, either sequentially or simultaneously.

Unfortunately, there is no concept of a key team in the database; by that, I mean the ability to identify the team that has case responsibility for that SU at that point in time. This would mean that in the case of a SU involved with multiple teams, it would be the main social work team instead of a rehabilitation team or a non-social work team like Finance or Legal Affairs.

To sum up:

Service users (SUs) with services active during a period with defined start and end dates. The SU can be current with an active service with no end date or an end date that falls after the defined period end, or can be historic with no service open - all services having ended during the time period.

Social work teams and attached social workers who are allocated to the SU. These are involvements with a start and end date. Social workers are attached to a team (which may change). There can be a single involvement, or multiple involvements and the involvement data is the Service User ID, Professional Involvement ID, Professional/Team Name, Is Group? flag, Team, Involvement Start Date and Involvement End Date (which can be null). The involvement team is the Professional/Team name if Is Group?=Y, or Team if Is Group?=N.

What I am trying to end up with is a single team name that is the team or the social worker’s team that is the current or most recently active involvement with the service user. If there are multiple possibilities, I’m following the criteria from my original post to prioritise which involvement is selected. I’ve tried to do some of this prioritisation in the query by querying for each possibility in turn, but I’ve had to do that as separate queries rather than a single union query and merging the results in the report. I suppose I will have to try the union query route, but it will be a headache to set up the subqueries for unions 2, 3, and 4 given that union 2 excludes SUs with involvements in union 1, union 3 excludes SUs with involvements in 1 or 2, and union 4 excludes SUs with involvements in unions 1, 2 or 3.

Actually, I’ve just had a thought - keep the 4 separate queries, and merge them in a 5th query using the involvement IDs returned in the 4 queries and use the 5th query to synchronise with the SU IDs in the main pool query. I’ll have to try that when the data warehouse is live again, although that will mean the report is now close to 35 linked queries…

[dtolley]

From what you’ve explained, not sure why you need multiple queries or even need to merge data. Sounds like you should be able to do it in one simple query and a few variables. But, maybe I’m missing something. Really need to see the data to understand what you have going on. De-identify the data by changing out PHI or make it up.

[Maddye]

Multiple queries because of the numbers of rows and our creaking infrastructure…

Bear in mind I am dealing social care data which can go back 20+ years (some people have been ‘on the books’ since before birth), and there can be several hundred involvements per year (99% irrelevant for my purposes). To restrict the rows, I use active in period and limit it to a specific involvement role, and I’m afraid that around 10% of service users have no active involvements of that role in the time period.

Yes, I could extract the entire involvement history of someone with an active service in the year, but I would be working at the limits of what’s possible. I’ve already had issues with partial data in another area with less data.

Eventually, this will be done in SQL and PowerBI, but that will take another year to build the data platform. In the mean time, I have monthly reports to run…