March 28, 2017 at 8:07 am
Hi,
New to the group, and been looking around for a solution to this issue, and have found serveral possibilites but all having their own down falls, so not sure which way would be be the best practice for accomplishing this.
I have a table which contains a persons stats for a day from a phone system, each record contains a person's specific id, the date of the record, the current position in the company of the person and what formula is to be used for this particular record based on the person's position. Now if it stopped there it is pretty easy and straight forward, the issue comes in where the person in mid stream could change position at any given time, policy is that any position changes begin on the first of the month, but is not always the case depending on the need of the company, that is why I tag each record with the formula id of which one is associated to the position for that person on the given day. Now the formula shouldn't change from fiscal year to fiscal year. Each position within the department corresponds to a formula for calculating a particular key performance indicator(KPI), so if there are 5 positions then there would possibly be 3-5 different formulas to correspond to these positions, to add to the complexity of the issue, I will need to take these KPI's and aggregate them through possibly a weekly, definitely a monthly and 3 month average. Then at the end of the fiscal year an annual.
The processes I have investigated into accomplishing this is dynamic SQL, and cursor, or it maybe a combination of the two, or maybe there is something else I am missing. My thoughts are to bring the data together into a temp table, pass each record through a cursor to get the formula associated with the record, retrieve the correct formula and plug it int a variable, that is used in an SQL statement then insert the final records into another temp table for out put to the client side which would be an ASP.Net page. So though the records count that it would be working with on a daily bases would be small to start out with it will grow quite large. Daily the records will be between 100-250 records, multiply that by 7 days when doing the weekly, by 30 days for a monthly, 90 days for the 3 month, and 365 days for the annual, and so on, as you can see the records quickly get into the thousands, for an annual it could be approximately 73000 records. Now, I will be group by position and conditioning the data as far down as I can before I start processing it through the cursor, so that 73000 could be cut down quite substantially before I run it through.
So the question is, am I on the right track or is there a better way to do this?
March 28, 2017 at 9:08 am
jmcnorrill - Tuesday, March 28, 2017 8:07 AMHi,New to the group, and been looking around for a solution to this issue, and have found serveral possibilites but all having their own down falls, so not sure which way would be be the best practice for accomplishing this.
I have a table which contains a persons stats for a day from a phone system, each record contains a person's specific id, the date of the record, the current position in the company of the person and what formula is to be used for this particular record based on the person's position. Now if it stopped there it is pretty easy and straight forward, the issue comes in where the person in mid stream could change position at any given time, policy is that any position changes begin on the first of the month, but is not always the case depending on the need of the company, that is why I tag each record with the formula id of which one is associated to the position for that person on the given day. Now the formula shouldn't change from fiscal year to fiscal year. Each position within the department corresponds to a formula for calculating a particular key performance indicator(KPI), so if there are 5 positions then there would possibly be 3-5 different formulas to correspond to these positions, to add to the complexity of the issue, I will need to take these KPI's and aggregate them through possibly a weekly, definitely a monthly and 3 month average. Then at the end of the fiscal year an annual.
The processes I have investigated into accomplishing this is dynamic SQL, and cursor, or it maybe a combination of the two, or maybe there is something else I am missing. My thoughts are to bring the data together into a temp table, pass each record through a cursor to get the formula associated with the record, retrieve the correct formula and plug it int a variable, that is used in an SQL statement then insert the final records into another temp table for out put to the client side which would be an ASP.Net page. So though the records count that it would be working with on a daily bases would be small to start out with it will grow quite large. Daily the records will be between 100-250 records, multiply that by 7 days when doing the weekly, by 30 days for a monthly, 90 days for the 3 month, and 365 days for the annual, and so on, as you can see the records quickly get into the thousands, for an annual it could be approximately 73000 records. Now, I will be group by position and conditioning the data as far down as I can before I start processing it through the cursor, so that 73000 could be cut down quite substantially before I run it through.
So the question is, am I on the right track or is there a better way to do this?
I haven't heard anything that suggests you need a cursor. Of course, I don't have any sample data to see what kind of representation your "formula" has in the database, which might be rather important, so the answer is, "it depends". If you want a USEFUL answer, you'll have to provide more detail. Generally, cursors are not usually necessary, but good database design can be a critical factor in keeping it that way. If you can post your table create statements and some sample data for each of the tables, and what your current query to report on the data looks like, we can get into a lot more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 9:41 am
OK, here is a sample of the data.
The records that I will be using will be something like the below.
| PayRollID | PositionID | Supervisor | RecordDate | Staffed_Time | ACD_Calls | ACD_Time | Avg_ACD_Time | Break | Lunch | Training | Meeting | Proj_Sales | Chat | Proj_Net | Proj_Process | GC_Work | Email_Closes | FraudTouches | FraudCB | FormulaID |
| 141418 | 7 | 60625 | 3/15/2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 151439 | 5 | 84910 | 3/15/2017 | 28378 | 42 | 16619 | 421.881 | 1756 | 3506 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 150772 | 5 | 84910 | 3/15/2017 | 29140 | 94 | 18660 | 198.5106 | 1934 | 3593 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 111102 | 5 | 135988 | 3/15/2017 | 27402 | 93 | 16343 | 175.7312 | 1470 | 3609 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 151371 | 7 | 105130 | 3/15/2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 104792 | 4 | 60625 | 3/15/2017 | 32100 | 0 | 0 | 0 | 2380 | 3798 | 0 | 0 | 0 | 0 | 0 | 25912 | 0 | 0 | 0 | 0 | 2 |
The Formula would be something along the lines of
| FormulaID | PositionID | KIP | KPIFormula |
| 2 | 5 | Productivity | ([Staffed_Time]/[ACD_Time])/3600 |
| 3 | 7 | Productivity | ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600 |
So with the above data I would need to process each record in the data table by a formula in the Formula table based on what position the person was when the record was submitted. Then group it together by the person and the postion(s) that they were in for a given date range. Hope this adds a bit of clarity to what I am trying to accomplish, if more is needed let me know. This is a learning practice for me as much of a job project, if you can see improvements of database/table layout and how to make it work better/more efficiently please let me know, the complexity of tackling this is really pushing the limits of my knowledge so anything that would make it a more productive application would be greatly appreciated, this a ground up project so I am not currently tided to how it has to be done, just the data that I have to use.
March 28, 2017 at 11:04 am
jmcnorrill - Tuesday, March 28, 2017 9:41 AMOK, here is a sample of the data.The records that I will be using will be something like the below.
PayRollID PositionID Supervisor RecordDate Staffed_Time ACD_Calls ACD_Time Avg_ACD_Time Break Lunch Training Meeting Proj_Sales Chat Proj_Net Proj_Process GC_Work Email_Closes FraudTouches FraudCB FormulaID 141418 7 60625 3/15/2017 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 151439 5 84910 3/15/2017 28378 42 16619 421.881 1756 3506 0 0 0 0 0 0 0 0 0 0 2 150772 5 84910 3/15/2017 29140 94 18660 198.5106 1934 3593 0 0 0 0 0 0 0 0 0 0 2 111102 5 135988 3/15/2017 27402 93 16343 175.7312 1470 3609 0 0 0 0 0 0 0 0 0 0 2 151371 7 105130 3/15/2017 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 104792 4 60625 3/15/2017 32100 0 0 0 2380 3798 0 0 0 0 0 25912 0 0 0 0 2 The Formula would be something along the lines of
FormulaID PositionID KIP KPIFormula 2 5 Productivity ([Staffed_Time]/[ACD_Time])/3600 3 7 Productivity ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600 So with the above data I would need to process each record in the data table by a formula in the Formula table based on what position the person was when the record was submitted. Then group it together by the person and the postion(s) that they were in for a given date range. Hope this adds a bit of clarity to what I am trying to accomplish, if more is needed let me know. This is a learning practice for me as much of a job project, if you can see improvements of database/table layout and how to make it work better/more efficiently please let me know, the complexity of tackling this is really pushing the limits of my knowledge so anything that would make it a more productive application would be greatly appreciated, this a ground up project so I am not currently tided to how it has to be done, just the data that I have to use.
Unfortunately this does need to be dynamic sql and this honestly gets into the realm of not hitting on BP. You have sql statements that are stored in the table which have to be pulled into a query to help build future sql statements and then process data.
What is the business or even technology reason for storing these sql statements (formulas) within a lookup table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2017 at 11:21 am
Okay... first order of business is to ensure you have a table that represents when a given person (I assume this is represented by PayRollID occupied each PositionID. Have you given that any thought as yet? I'm thinking something like the following:CREATE TABLE dbo.PersonPositionMap (
PersonPositionMapID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PayRollID int NOT NULL, --Good candidate for foreign key to table holding PayRollID values.
PositionID int NOT NULL, --The position they occupied for the timeframe associate with this record.
PositionStart date NOT NULL, --The date they started this position.
PositionEnd date NOT NULL --The date they ended this position.
);
CREATE NONCLUSTERED INDEX IX_PersonPositionMap_PositionStart_PositionEnd_INCLUDES_PayRollID_PositionID ON dbo.PersonPositionMap
(
PositionStart ASC,
PositionEnd ASC
)
INCLUDE
(
PayRollID,
PositionID
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 11:45 am
What tools are you using to build this out to display to end users? And does the formula table change often? If the formula table will change very infrequently or never, you could just hard-code in the KPIFormula into your query with something like:
SELECT *, CASE WHEN PositionID = 5 THEN ([Staffed_Time]/ACD_Time])/3600 ELSE WHEN PositionID = 7 THEN ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600
FROM EmployeeTable
Mind you, I'd avoid the "SELECT *" and put in column names, I just did that to keep it short.
I would recommend building the above into a view and that should remove the reliance on:
1 - the second table
2 - cursors
IF using the second table is a requirement (ie your application writes to table 2 for doing the calculations for example), then I think cursors would be your best bet.
One thing to note with using those formulas though, dividing by 0 will give you errors and your sample data will cause division by zero for all PositionID's of 7 in the ACD_Time column. I would put a constraint on the column or have some sanity checks for that ACD_Time column calculations.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 28, 2017 at 11:53 am
SQLRNNR - Tuesday, March 28, 2017 11:04 AMjmcnorrill - Tuesday, March 28, 2017 9:41 AMOK, here is a sample of the data.The records that I will be using will be something like the below.
PayRollID PositionID Supervisor RecordDate Staffed_Time ACD_Calls ACD_Time Avg_ACD_Time Break Lunch Training Meeting Proj_Sales Chat Proj_Net Proj_Process GC_Work Email_Closes FraudTouches FraudCB FormulaID 141418 7 60625 3/15/2017 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 151439 5 84910 3/15/2017 28378 42 16619 421.881 1756 3506 0 0 0 0 0 0 0 0 0 0 2 150772 5 84910 3/15/2017 29140 94 18660 198.5106 1934 3593 0 0 0 0 0 0 0 0 0 0 2 111102 5 135988 3/15/2017 27402 93 16343 175.7312 1470 3609 0 0 0 0 0 0 0 0 0 0 2 151371 7 105130 3/15/2017 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 104792 4 60625 3/15/2017 32100 0 0 0 2380 3798 0 0 0 0 0 25912 0 0 0 0 2 The Formula would be something along the lines of
FormulaID PositionID KIP KPIFormula 2 5 Productivity ([Staffed_Time]/[ACD_Time])/3600 3 7 Productivity ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600 So with the above data I would need to process each record in the data table by a formula in the Formula table based on what position the person was when the record was submitted. Then group it together by the person and the postion(s) that they were in for a given date range. Hope this adds a bit of clarity to what I am trying to accomplish, if more is needed let me know. This is a learning practice for me as much of a job project, if you can see improvements of database/table layout and how to make it work better/more efficiently please let me know, the complexity of tackling this is really pushing the limits of my knowledge so anything that would make it a more productive application would be greatly appreciated, this a ground up project so I am not currently tided to how it has to be done, just the data that I have to use.
Unfortunately this does need to be dynamic sql and this honestly gets into the realm of not hitting on BP. You have sql statements that are stored in the table which have to be pulled into a query to help build future sql statements and then process data.
What is the business or even technology reason for storing these sql statements (formulas) within a lookup table?
The reason behind this, it that I will be generating reporting of KIP's for a person that will/can span multiple months/years encompassing multiple positions through the coarse of a year that needs ultimately at the end of the FY will be representative of the persons contribution to the company, so these formulas that will be imposed into an SQL statement have to be fluid and a standard hard coded formula will not work. From one year to the next the formula is going to possible change, from position to position the formula will change and include different aspects as the different position does differing rolls that have other elements of the KPI. So the formula needs to be associated with the record and then the data calculated based on what ever the formula was at the time of the interaction. If it is hard coded into the system then when it is updated all history for that change is changed as well. It may be that I need to calculate the KPI's and just write them into a table to report on instead of doing everything on the fly when the data is requested. Why I ask is there a better way of doing this, on the fly, conditioned and stored separately, just begs the question of processor ticks versus data storage space on the server.
March 28, 2017 at 11:56 am
sgmunson - Tuesday, March 28, 2017 11:21 AMOkay... first order of business is to ensure you have a table that represents when a given person (I assume this is represented by PayRollID occupied each PositionID. Have you given that any thought as yet? I'm thinking something like the following:CREATE TABLE dbo.PersonPositionMap (
PersonPositionMapID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PayRollID int NOT NULL, --Good candidate for foreign key to table holding PayRollID values.
PositionID int NOT NULL, --The position they occupied for the timeframe associate with this record.
PositionStart date NOT NULL, --The date they started this position.
PositionEnd date NOT NULL --The date they ended this position.
);
CREATE NONCLUSTERED INDEX IX_PersonPositionMap_PositionStart_PositionEnd_INCLUDES_PayRollID_PositionID ON dbo.PersonPositionMap
(
PositionStart ASC,
PositionEnd ASC
)
INCLUDE
(
PayRollID,
PositionID
);
In my thinking, there is an Employee table that houses most of the information as to what position the person is in at the current time of the data record and what I use to determine what formual(s) to associate the record with. This would give me the date range of when the employee would have been in that position, or am I missing something?
March 28, 2017 at 12:09 pm
bmg002 - Tuesday, March 28, 2017 11:45 AMWhat tools are you using to build this out to display to end users? And does the formula table change often? If the formula table will change very infrequently or never, you could just hard-code in the KPIFormula into your query with something like:
SELECT *, CASE WHEN PositionID = 5 THEN ([Staffed_Time]/ACD_Time])/3600 ELSE WHEN PositionID = 7 THEN ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600
FROM EmployeeTableMind you, I'd avoid the "SELECT *" and put in column names, I just did that to keep it short.
I would recommend building the above into a view and that should remove the reliance on:
1 - the second table
2 - cursorsIF using the second table is a requirement (ie your application writes to table 2 for doing the calculations for example), then I think cursors would be your best bet.
One thing to note with using those formulas though, dividing by 0 will give you errors and your sample data will cause division by zero for all PositionID's of 7 in the ACD_Time column. I would put a constraint on the column or have some sanity checks for that ACD_Time column calculations.
There are some positions that do not use or care about the ACD time which is the cumulative time a person is on an (Automatically Delivered Call) so a typical phone agent would have this, but a person in the position of not being on the phone would typically not have that in the formula, but I understand what you mean by making sure I address that in the calculations. As to hard coding the formula into the query, each record according to the person could have a different position and in some cases the two people in different positions may have the same KPI we'll just say "Productivity" the crux is that that Productivity may be calculated in 2 differing ways depending on the position. So unless I create something like Temp tables for each position with in the stored procedure to do the calculations then bring them together for output, then the SQL statement that does the calculations has to be fluid. I am building this with as much forethought into the process is possible, so if a position is added it's not a scrap and rebuild or highly modify existing procedures to correct that, if it can be avoided.
P.S. for got to answer a couple of your first questions.
I will be using an ASP.Net page as the front end to display the data.
The formula does not change often, but it can change mid year, but usually only changes annually and that may only be every 2-3 years. The problem with hard coding is that it would change the results of previous data once changed so there would be no archival record to to with. Which could possible mean putting the conditioned data into it's own table for reporting and display purposes.
March 28, 2017 at 12:10 pm
jmcnorrill - Tuesday, March 28, 2017 11:56 AMsgmunson - Tuesday, March 28, 2017 11:21 AMOkay... first order of business is to ensure you have a table that represents when a given person (I assume this is represented by PayRollID occupied each PositionID. Have you given that any thought as yet? I'm thinking something like the following:CREATE TABLE dbo.PersonPositionMap (
PersonPositionMapID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PayRollID int NOT NULL, --Good candidate for foreign key to table holding PayRollID values.
PositionID int NOT NULL, --The position they occupied for the timeframe associate with this record.
PositionStart date NOT NULL, --The date they started this position.
PositionEnd date NOT NULL --The date they ended this position.
);
CREATE NONCLUSTERED INDEX IX_PersonPositionMap_PositionStart_PositionEnd_INCLUDES_PayRollID_PositionID ON dbo.PersonPositionMap
(
PositionStart ASC,
PositionEnd ASC
)
INCLUDE
(
PayRollID,
PositionID
);In my thinking, there is an Employee table that houses most of the information as to what position the person is in at the current time of the data record and what I use to determine what formual(s) to associate the record with. This would give me the date range of when the employee would have been in that position, or am I missing something?
Yes, you may be missing something. You can't be counting data from a time period in which a person was not in a given position as operating under the current formula, so without history on when a given person occupied a given position, none of this is going to work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 12:27 pm
sgmunson - Tuesday, March 28, 2017 12:10 PMjmcnorrill - Tuesday, March 28, 2017 11:56 AMsgmunson - Tuesday, March 28, 2017 11:21 AMOkay... first order of business is to ensure you have a table that represents when a given person (I assume this is represented by PayRollID occupied each PositionID. Have you given that any thought as yet? I'm thinking something like the following:CREATE TABLE dbo.PersonPositionMap (
PersonPositionMapID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PayRollID int NOT NULL, --Good candidate for foreign key to table holding PayRollID values.
PositionID int NOT NULL, --The position they occupied for the timeframe associate with this record.
PositionStart date NOT NULL, --The date they started this position.
PositionEnd date NOT NULL --The date they ended this position.
);
CREATE NONCLUSTERED INDEX IX_PersonPositionMap_PositionStart_PositionEnd_INCLUDES_PayRollID_PositionID ON dbo.PersonPositionMap
(
PositionStart ASC,
PositionEnd ASC
)
INCLUDE
(
PayRollID,
PositionID
);In my thinking, there is an Employee table that houses most of the information as to what position the person is in at the current time of the data record and what I use to determine what formual(s) to associate the record with. This would give me the date range of when the employee would have been in that position, or am I missing something?
Yes, you may be missing something. You can't be counting data from a time period in which a person was not in a given position as operating under the current formula, so without history on when a given person occupied a given position, none of this is going to work.
Currently each record that I pull into the data base would have a date and position attached to it, so once I have filtered out the all the records I need within a date range the only thing that becomes of value is the total records that in that dataset by position. So a statement like below should give me the information you are referring to correct? For the most part I will be aggregating information from a daily to a weekly and up from there there, the time in position should be handled nicely since these will be totals added, subtracted, divided by or averaged of each other, depending on the date method I and attempting to calculate I only need to aggragate by the week, month or year to total up for the X person in Y position(s).
SELECT PayrollID, MAX(RecordDate) AS StartDate, MIN(RecordDate) As EndDate, PostiionID
FROM tbl_GPA_DATA
GROUP BY PayRollID, PositionID
March 28, 2017 at 1:44 pm
Looks rather straightforward, since the detail table already contains the Position the person was in on that given day.
If you could post useable sample data -- CREATE TABLE and INSERT statements -- for the sample data above, I could work up a query. I do think you'll need dynamic SQL to dynamically compute the formula, I don't see any easy way around that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 28, 2017 at 2:50 pm
jmcnorrill - Tuesday, March 28, 2017 12:09 PMbmg002 - Tuesday, March 28, 2017 11:45 AMWhat tools are you using to build this out to display to end users? And does the formula table change often? If the formula table will change very infrequently or never, you could just hard-code in the KPIFormula into your query with something like:
SELECT *, CASE WHEN PositionID = 5 THEN ([Staffed_Time]/ACD_Time])/3600 ELSE WHEN PositionID = 7 THEN ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600
FROM EmployeeTableMind you, I'd avoid the "SELECT *" and put in column names, I just did that to keep it short.
I would recommend building the above into a view and that should remove the reliance on:
1 - the second table
2 - cursorsIF using the second table is a requirement (ie your application writes to table 2 for doing the calculations for example), then I think cursors would be your best bet.
One thing to note with using those formulas though, dividing by 0 will give you errors and your sample data will cause division by zero for all PositionID's of 7 in the ACD_Time column. I would put a constraint on the column or have some sanity checks for that ACD_Time column calculations.
There are some positions that do not use or care about the ACD time which is the cumulative time a person is on an (Automatically Delivered Call) so a typical phone agent would have this, but a person in the position of not being on the phone would typically not have that in the formula, but I understand what you mean by making sure I address that in the calculations. As to hard coding the formula into the query, each record according to the person could have a different position and in some cases the two people in different positions may have the same KPI we'll just say "Productivity" the crux is that that Productivity may be calculated in 2 differing ways depending on the position. So unless I create something like Temp tables for each position with in the stored procedure to do the calculations then bring them together for output, then the SQL statement that does the calculations has to be fluid. I am building this with as much forethought into the process is possible, so if a position is added it's not a scrap and rebuild or highly modify existing procedures to correct that, if it can be avoided.
I understand that, but the above query I posted handles the formula based on the PositionID column.
Mind you, to me this feels like something that should be handled at the application level, not the database level. That is, I am assuming this is going to be pulled into some sort of report. I think that this sort of logic should exist in the report, not in the database. But if storing it in the database is the preferred solution, is it that difficult to update a view to have 1 additional value in a CASE statement vs updating a table and hoping that it doesn't somehow break your dynamic SQL?
My conern with your method is that you can't actually test things without throwing data into that second table. But if you build it as a CASE statement inside of a view, you can run it outside of the view and see the results (and errors if any occur) before altering the view.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 28, 2017 at 3:10 pm
bmg002 - Tuesday, March 28, 2017 2:50 PMjmcnorrill - Tuesday, March 28, 2017 12:09 PMbmg002 - Tuesday, March 28, 2017 11:45 AMWhat tools are you using to build this out to display to end users? And does the formula table change often? If the formula table will change very infrequently or never, you could just hard-code in the KPIFormula into your query with something like:
SELECT *, CASE WHEN PositionID = 5 THEN ([Staffed_Time]/ACD_Time])/3600 ELSE WHEN PositionID = 7 THEN ([Staffed_Time]-([Break]+[Lunch]+[Meeting]))/[ACD_Time])/3600
FROM EmployeeTableMind you, I'd avoid the "SELECT *" and put in column names, I just did that to keep it short.
I would recommend building the above into a view and that should remove the reliance on:
1 - the second table
2 - cursorsIF using the second table is a requirement (ie your application writes to table 2 for doing the calculations for example), then I think cursors would be your best bet.
One thing to note with using those formulas though, dividing by 0 will give you errors and your sample data will cause division by zero for all PositionID's of 7 in the ACD_Time column. I would put a constraint on the column or have some sanity checks for that ACD_Time column calculations.
There are some positions that do not use or care about the ACD time which is the cumulative time a person is on an (Automatically Delivered Call) so a typical phone agent would have this, but a person in the position of not being on the phone would typically not have that in the formula, but I understand what you mean by making sure I address that in the calculations. As to hard coding the formula into the query, each record according to the person could have a different position and in some cases the two people in different positions may have the same KPI we'll just say "Productivity" the crux is that that Productivity may be calculated in 2 differing ways depending on the position. So unless I create something like Temp tables for each position with in the stored procedure to do the calculations then bring them together for output, then the SQL statement that does the calculations has to be fluid. I am building this with as much forethought into the process is possible, so if a position is added it's not a scrap and rebuild or highly modify existing procedures to correct that, if it can be avoided.
I understand that, but the above query I posted handles the formula based on the PositionID column.
Mind you, to me this feels like something that should be handled at the application level, not the database level. That is, I am assuming this is going to be pulled into some sort of report. I think that this sort of logic should exist in the report, not in the database. But if storing it in the database is the preferred solution, is it that difficult to update a view to have 1 additional value in a CASE statement vs updating a table and hoping that it doesn't somehow break your dynamic SQL?
My conern with your method is that you can't actually test things without throwing data into that second table. But if you build it as a CASE statement inside of a view, you can run it outside of the view and see the results (and errors if any occur) before altering the view.
I understand the view portion, and I will probably use a view to test out the calculations before I actually arrive at a final product, The issue with hard coding this, even with a CASE statement is that if the formula changes for any given KPI then the CASE statement on the SQL server will have to be updated, I am trying to avoid having a user have to come to me if there is a change 3 days, 3 months or even 3 years from now If I am no longer working for the company. Also if I want to preserve calculations from a previous time, lets just say I have 3 months of data and on the 4th month the formula is changed, the previous 3 months need to report on the old code where the coming month needs to report on the new code. No this can be handled in the case by looking at the date of the record and creating the case that between such and such date use the old code, or use the new code, but that gets to be a very long and convoluted case statement has changes are done, which I am trying to avoid.
As to the part of it being handled by the Application level and not at the DB, is a valid one, it is something I will need to look into, it's just that ASP is great at minor data transformation and pretty easy straight forward calculations but when it gets complex on organizing and transforming the data it can be a bit cumbersome from multiple tables and possibly more complex query building, I am neither a master ASP builder or a SQL guru, I have just enough knowledge in both to be extremely dangerous. 🙂
March 28, 2017 at 3:12 pm
Another option could be to use a static view that is dynamically generated for the formulas. When a formula(s) change, the view is regenerated. Just an idea.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply