Leroy Leroy - 1 year ago 47
SQL Question

Replace parts of string formula with relevant values

I have a table (

tbl_Composites
) which contains formulas that are used to calculate performance scores. e.g.

KPI | FORMULA |
--------------------------------------------------
Productivity | [ItemsCompleted]/[LoggedInTime] |
SalesConversion| [Sales]+[Leads]/[TotalCalls] |


There is another table (
tbl_BaseData
) which has a column which contains these metrics and the associated value for each person

SCOREDATE | USERID | METRIC | VALUE |
------------------------------------------------
01/03/2017 | 20511 | ItemsCompleted | 50 |
01/03/2017 | 20511 | LoggedInTime | 320 |
01/03/2017 | 20630 | Sales | 8 |
01/03/2017 | 20630 | Leads | 3 |
01/03/2017 | 20630 | TotalCalls | 25 |


I am trying to write a VBA function that will take the parameters of the Date, UserID and the KPI and then get the relevant metric values for that KPI formula and calculate the result.

I have something I've managed to put together but it's not very efficient and it doesn't work if there is no record for one of the metrics (e.g someone took calls but made no sales).

Function CalulateFormula(strFormula As String, userID As String, scoreDate As Date) As String
Dim Metric As String, valueList As String
Dim i As Integer

For i = 1 To Len(strFormula)
If Mid(strFormula, i, 1) = "[" Then
Metric = Mid(strFormula, i + 1, InStr(i + 1, strFormula, "]") - i - 1)
valueList = valueList & "'" & Metric & "', "
CalulateFormula = CalulateFormula & Metric
i = InStr(i + 1, strFormula, "]")
Else
CalulateFormula = CalulateFormula & Mid(strFormula, i, 1)
End If
Next i

valueList = Left(valueList, Len(valueList) - 2)

CalulateFormula = EvaluateSQL(CalulateFormula, valueList, userID, scoreDate)

End Function


This will build the formula string and populate a list of the metrics that it will need to find. It then calls another function as below, which runs some SQL to get the metric values and evaluate the formula.

Function EvaluateSQL(CalulateFormula As String, valueList As String, userID As String, scoreDate As Date) As String
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT Metric, metricValue " & _
"FROM tbl_BaseData " & _
"WHERE metric in (" & valueList & ") AND userID = '" & userID & "' AND scoreDate = #" & Format(scoreDate, "mm/dd/yyyy") & "# "

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
CalulateFormula = Replace(CalulateFormula, rs!Metric, rs!metricValue)
rs.MoveNext
Loop
EvaluateSQL = Eval(CalulateFormula)
End Function


This seems like quite a roundabout way of doing things and I'm wondering if I need to approach the problem from a completely different angle. Could anyone advise me if there is a simpler / more efficient way that will also be able to handle null / missing values?

Answer Source

It looks like you're overthinking your string generation. Your values for FORMULA already contain the calculation operation /, so you can read the full value of FORMULA as your SQL calculation. It looks like a lot of your concern is with the brackets in your FORMULA values, but SQL will read [ItemsCompleted] as a valid field name for a calculation.

Better than the above, you can do this entirely in SQL code.

What you really need here is a single line per user per day. With that you can easily run your metrics; you don't even need the tbl_Composites table.

So here's how you do it

SELECT
    SCOREDATE
    ,USERID
    ,ItemsCompleted / LoggedInTime as Productivity
    ,Sales + Leads as SalesConversion
    from
    (SELECT distinct 
        base.SCOREDATE, 
        base.USERID, 
        ic.ItemsCompleted,
        lt.LoggedInTime,
        sls.Sales,
        lds.Leads,
        tc.TotalCalls
    from (((((
    tbl_BaseData as base
        left join
            (SELECT SCOREDATE, USERID, VALUE as ItemsCompleted
            from tbl_BaseData
            where METRIC = 'ItemsCompleted') as ic
            on base.USERID = ic.USERID
                and base.SCOREDATE = ic.SCOREDATE)
        left join
            (SELECT SCOREDATE, USERID, VALUE as LoggedInTime
            from tbl_BaseData
            where METRIC = 'LoggedInTime') as lt
            on base.USERID = lt.USERID
                and base.SCOREDATE = lt.SCOREDATE)
        left join
            (SELECT SCOREDATE, USERID, VALUE as Sales
            from tbl_BaseData
            where METRIC = 'Sales') as sls
            on base.USERID = sls.USERID
                and base.SCOREDATE = sls.SCOREDATE)
        left join
            (SELECT SCOREDATE, USERID, VALUE as Leads
            from tbl_BaseData
            where METRIC = 'Leads') as lds
            on base.USERID = lds.USERID
                and base.SCOREDATE = lds.SCOREDATE)
        left join
            (SELECT SCOREDATE, USERID, VALUE as TotalCalls
            from tbl_BaseData
            where METRIC = 'TotalCalls') as tc
            on base.USERID = tc.USERID
                and base.SCOREDATE = tc.SCOREDATE))
        as p

The inner query does the work of consolidating your data to one line per User, and accounts for nonexistent record types. Alone it returns

SCOREDATE   USERID  ItemsCompleted   LoggedInTime  Sales  Leads   TotalCalls
01/03/2017  20511   50               320           NULL   NULL    NULL
01/03/2017  20630   NULL             NULL          8      3       25

The outer query then calculates Productivity and SalesConversion. You can add lines for whatever other metric you need. It returns

SCOREDATE   USERID    Productivity    SalesConversion
01/03/2017  20511     0.156           NULL
01/03/2017  20630     NULL            11
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download