• ScottPletcher - Tuesday, March 28, 2017 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.

    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,>)