calculating change in a set of KPI

[etk]

I have the following query, which gathers a set of kpi for an employee across two reporting periods.

PARAMETERS [Enter_ID] Long, [first_period] Long, [second_period] Long;
SELECT *
FROM kpi
WHERE PERIOD_ID in ([first_period], [second_period])
AND Manger_ID = [Enter_ID];

What I hope to accomplish is to calculate the change from one period to the next. So, say we had Jun 2013 and July 2013 as reporting periods I would want to subtract each KPI in June from the same KPI in July and report on change in that value, for each employee ID. Is this something that can be accomplished with SQL or would I have to use VBA to do this?

For simplicity we will say the only fields in the kpi table are EMP_ID, PERIOD_ID, Manager_ID and the 8 KPI: X1, X2...X8

 

[plog]

First I would be remiss if I didn't yell at you for your improperly structured database. Numerated fields (X1, X2, X3) are a tell tale sign of a bad structure. Instead of going across, you should go down--you add records to a database, not columns.

Now for your issue, SQL can handle it, but you'll probably need a sub-query. The subquery would have all the fields of kpi table plus an additional one that determines the prior period, it would look like this:

SELECT *, (PERIOD_ID - 1) AS PriorPeriod FROM kpi;

Then you create a query based on that and kpi. You link kpi.PERIOD_ID to the PriorPeriod field of the above query and then EMP_ID to EMP_ID and Manager_ID together in each table.

 

[etk]

First I would be remiss if I didn't yell at you for your improperly structured database. Numerated fields (X1, X2, X3) are a tell tale sign of a bad structure. Instead of going across, you should go down--you add records to a database, not columns.

Can you elaborate? I had thought of building it with 8 tables, one for each KPI, but I am a bit limited, both by time and the way the data is sent to me. However, I am curious from a learning perspective. Are you able to illustrate an example of how you might structure it differently?

Let's say that each period is described by a week, starting Sunday and we have 2 KPI instead of 8 and X1 and X2 are just labels that describe incorrect jobs per job and revenue per job, and finally the manager ID does not exist in this simplification as it can be joined from an employee table instead. How would you structure this so that you could report on changes in KPI from one period to the next, by employee, or a subsequent join and then by manager?

 

[plog]

This is what you currently have, along with a sample record:

kpi
EMP_ID, PERIOD_ID, Manager_ID, X1, X2, , X4
17, 13, 901, Good, Poor, Average, Good

My suggestions would be to move the X values out and put them in another table. That will require that every record in kpi have a unique field so that we can link kpi and this new table I am talking about. This is what kpi should look like with that same sample record:

kpi
KPI_ID, EMP_ID, PERIOD_ID, Manager_ID
33, 17, 13, 901

This is what that new table would look like, with the same sample data as initially:

kpi_values
KPI_ID, KPI_Value, KPI_Type
33, Good, 1
33, Poor, 2
33, Average, 3
33, Good, 4

KPI_ID would be from the kpi table, KPI_Value would hold whatever is in the X value currently, and KPI_TYPE would hole the numeric part of the X that is now currently in the name of the field.

 

[etk]

I see what you are saying. However, I think my mistake is to use the X1, X2...Xn as placeholders in this thread. I did that for privacy reasons.

What I actually have is a table that looks like this

Emp_ID, Period_ID, Manger_ID, Job_Errors, Revenue,....etc

and data that looks like this:

1234, 41490, 5678, 5.68%, $21.25,....etc

If I grasp what you are saying corretly, I would create a table of KPI-ID's and then separate my 8 KPIs into separate tables, presumably with the primary key set to a composite of Emp_ID, Period_ID, and KPI_ID, instead of just the first two. The reason I haven't done this thus far is the importance of this db is not high enough to justify the time invested, and hence I put the data in the way it comes to me, roughly.

Perhaps this is helpful, as I was going to create a separate table to rank each KPI. Basically the requiments at this time is just to select the top 20% for performance improvement on each KPI, though I was hoping to suggest a model that observes the distribution and variance.

I hope I grasped what you were saying. feel free to correct me. Thanks!

 

[plog]

no. My tables still hold. Instead of of numeric KPI_Type values, it would hold your text "Job_Errors", "Revenue", etc. You don't want a seperate table for each type.

 

[etk]

I see what you are saying. One question, how do I observe the realtionship that the KPI value belongs to a certain Emp_ID for a certain Period_ID on the other table? Just add those foreign keys?

 

[etk]

Also are there not potential problems with data types in doing it as such? I mean for this it is just a formatting issues, ($ vs % vs integers), but there could be different data types used for measurement of various kpi, such as the 'good, bad, fair' example that you provided as opposed to say a numeric value that features in my data. What would you do in such a circumstance where you have numeric and non-numeric data across the various kpi.