March 28, 2017 at 3:18 pm
ScottPletcher - Tuesday, March 28, 2017 3:12 PMAnother 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.
HMMMM, interesting have any links for info about doing dynamic views, something I have not heard about before now?
March 28, 2017 at 3:22 pm
jmcnorrill - Tuesday, March 28, 2017 3:18 PMScottPletcher - Tuesday, March 28, 2017 3:12 PMAnother 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.HMMMM, interesting have any links for info about doing dynamic views, something I have not heard about before now?
Again, it's not really a dynamic view as much as it is a static view that is dynamically generated. With a formula table in a useable format -- CREATE TABLE and INSERT statements -- I would quickly demonstrate what I mean.
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 3:27 pm
jmcnorrill - Tuesday, March 28, 2017 3:10 PMbmg002 - 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. 🙂
Any reason not to use a reporting tool such as SAS or SSRS?
I think if you are basing it on a date, you are going to have a few case statements building up dynamic SQL in your second table too, no?
I think no matter what solution you go with, it is going to get complex quickly as more and more conditions start falling into scope.
Without actual data, it is tricky to know the best solution, but based on my experience, cursors are generally the slowest solution. Not always, but most of the time they are the slowest solution. Another thing, if you are using Dynamic SQL, watch for apostrphies in your dynamic SQL. Those can cause a lot of headache too.
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:30 pm
ScottPletcher - Tuesday, March 28, 2017 1:44 PMLooks 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.
This is the full code for the tables and an Insert, I cut the data short on the code I posted up top so you can just drop any columns that are not in data, I'll get the meaning from what you are planning on putting together.
CREATE TABLE [dbo].[tbl_GPA_Master_Totals](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[RecordDate] [datetime] NULL,
[PayRollID] [nchar](20) NOT NULL,
[PositionID] [int] NOT NULL,
[Supervisor] [nchar](20) NOT NULL,
[Staffed_Time] [int] NOT NULL,
[ACD_Calls] [int] NOT NULL,
[ACD_Time] [int] NOT NULL,
[Avg_ACD_Time] [float] NOT NULL,
[Break] [int] NOT NULL,
[Lunch] [int] NOT NULL,
[Training] [int] NOT NULL,
[Meeting] [int] NOT NULL,
[Proj_Sales] [int] NOT NULL,
[Chat] [int] NOT NULL,
[Proj_Net] [int] NOT NULL,
[Proj_Process] [int] NOT NULL,
[GC_Work] [int] NOT NULL,
[Email_Closes] [int] NOT NULL,
[FraudTouches] [int] NOT NULL,
[FraudCB] [int] NOT NULL,
[FormulaID] [int],
CONSTRAINT [PK_tbl_GPA_Master_Totals] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[tbl_GPA_Master_Totals]
([RecordDate]
,[PayRollID]
,[PositionID]
,[Supervisor]
,[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])
VALUES
(<RecordDate, datetime,>
,<PayRollID, nchar(20),>
,<PositionID, int,>
,<Supervisor, nchar(20),>
,<Staffed_Time, int,>
,<ACD_Calls, int,>
,<ACD_Time, int,>
,<Avg_ACD_Time, float,>
,<Break, int,>
,<Lunch, int,>
,<Training, int,>
,<Meeting, int,>
,<Proj_Sales, int,>
,<Chat, int,>
,<Proj_Net, int,>
,<Proj_Process, int,>
,<GC_Work, int,>
,<Email_Closes, int,>
,<FraudTouches, int,>
,<FraudCB, int,>
,<FormulaID, int,>)
CREATE TABLE [dbo].[tbl_GPA_Formulas](
[FormulaID] [int] IDENTITY(1,1) NOT NULL,
[PositionID] [int] NOT NULL,
[GPAFormula] [nvarchar](max) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_tbl_GPA_Formulas] PRIMARY KEY CLUSTERED
(
[FormulaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [dbo].[tbl_GPA_Formulas]
([PositionID]
,[GPAFormula]
,[IsActive])
VALUES
(<PositionID, int,>
,<GPAFormula, nvarchar(max),>
,<IsActive, bit,>)
March 29, 2017 at 7:33 am
bmg002 - Tuesday, March 28, 2017 3:27 PMjmcnorrill - Tuesday, March 28, 2017 3:10 PMbmg002 - 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. 🙂
Any reason not to use a reporting tool such as SAS or SSRS?
I think if you are basing it on a date, you are going to have a few case statements building up dynamic SQL in your second table too, no?
I think no matter what solution you go with, it is going to get complex quickly as more and more conditions start falling into scope.
Without actual data, it is tricky to know the best solution, but based on my experience, cursors are generally the slowest solution. Not always, but most of the time they are the slowest solution. Another thing, if you are using Dynamic SQL, watch for apostrphies in your dynamic SQL. Those can cause a lot of headache too.
Thanks for the advise. In what I was researching to do this, and executing dynamic SQL and Cursors, those were the pitfalls I came across with each, so I wanted to reach out and see what best practice would be to develop this.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply