Source Type SQL Metric Update Overwrites Existing values with Different Partition Values

[dataCollegechurch]

Description

Teams can use the 'Run Now/Calculate Metrics Job' to add Metric Data. They can also add data manually.
Lets pretend someone adds this manual metric data.

Here we have a metric called headcount and that metric has a partition that allows the user to specify a country.
We have entered one metric value with a MetricValueDateTime of 2/12/2025, a value of 50, and a country partion value of AF.

Next let's pretend we want to import some data via SQL.

The data we want to import has a similar MetricValueDateTime of 2/12/2025, but it has a different partition value with the country BO (Defined Value ID = 286)

If we click run now after setting up this SQL, we are hoping to see that we now have two metric values now. One for the country AF and one for the country BO.

The documentation doesn't make it clear how this situation will be handled because it doesn't mention how imported data with the same [MetricValueDateTime] but differing partitions will be handled. On Rock version 16.6 the end result of running the scenario above would be 2 metric values, 1 fo AF and 1 for BO. On Rock version 16.8 the metric value for BO overwrites the AF metric value.

Here is what the documention says will have in the Source SQL info bubble:
NOTE: If a [MetricValueDateTime] is specified and there is already a metric value, the value will get updated. This is handy if you have a weekly metric, but schedule it to calculate every day.

Actual Behavior

Metric values imported via Source SQL update existing values that have a matching MetricValueDateTime even when their partitions differ.

Expected Behavior

Metric values imported via Source SQL update existing values that have a matching MetricValueDateTime and matching partition values. Otherwise a new metric value row is added.

Steps to Reproduce

1. Create a new metric called Headcount with 1 partition called country
   
2. Add a Metric Value manually
   
   
3. Edit the Metric and set the Source Type to SQL with the following SQL value SELECT 50,'02/12/2025'AS [MetricValueDateTime],286
   
4. Click the Run Now Button
5. See how the existing value with a matching MetricValueDateTime but a different partition value was overwritten
   

Issue Confirmation

• Perform a search on the Github Issues to see if your bug or enhancement is already reported.
• Reproduced the problem on a fresh install or on the demo site.

Rock Version

16.8

Client Culture Setting

en-US