I am querying the Timberline replicated database located on the Kettler “ktlrhq-db1.corp.ketsco.com” SQL Server instance.

I need to perform several queries into this database.  This query returns total amounts for each cost code, section and job:

SELECT Job, Section, Cost_Code, Description, Group_Cost_Code, Status, Actual_Start_Date, Total_Estimate

, JTD_Cost, YTD_Cost, QTD_Cost, MTD_Cost

  FROM MASTER_JCM_COST_CODE

  WHERE Job IN (<comma-delimited list of Jobs>)

  ORDER BY 1,2,3

This returns accurate and complete information.  There are 149,679 records in the MASTER_JCM_COST_CODE table.  For the Frederickburg project (Job # 165-001) there are 87 records.

It is important to note that the replicated MASTER_JC_COST_CODE table does not have any Indexes.  To maximize data extraction speed, the MASTER_JCM_COST_CODE table should have indexes on the following fields:

• Job
• Section
• Cost_Code

The above query only provides “totals” for each “Job-Section-Cost Code” combination.

We also require the summation of the amount spent each MONTH for each “Job-Section-Cost Code” combination.  Based on my experience with another Sage installation, I believe this query will return the sum of all amounts spent each month for each “Job-Section-Cost Code” combination.  Note it is querying the CURRENT_JCT_TRANSACTION table.  There is another candidate table for this information, the HISTORY_JCT_TRANSACTION table.  Both of these tables have the same structure.  Either or both of these tables should contain the required information.

SELECT CAST(Accounting_Date - DAY(Accounting_Date) + 1 AS DATE), SUM(Amount)

  FROM CURRENT_JCT_TRANSACTION 

  WHERE Job  = '<Job Nbr>' AND Section  = '<Section>' AND Cost_Code  = '<Cost Code>'

  GROUP BY CAST(Accounting_Date - DAY(Accounting_Date) + 1 AS DATE)

  ORDER BY 1

In the above query, the CAST function translates the date of the transaction to the first day of the month of the transaction.  There may be a need to put an additional clause in the WHERE statement that restricts the TRANSACTION_TYPE.  I will determine that when I have some data in this table.

There are 0 records in both the CURRENT_JCT_TRANSACTION table and HISTORY_JCT_TRANSACTION table.

Whatever replication process that is populating the MASTER_JCM_COST_CODE table in the  “ktlrhq-db1.corp.ketsco.com” SQL Server instance must be extended to include either the CURRENT_JCT_TRANSACTION or HISTORY_JCT_TRANSACTION table (or both).  I suspect that the CURRENT_JCT_TRANSACTION and/or HISTORY_JCT_TRANSACTION tables will have an order of magnitude more records in them than the MASTER_JCM_COST_CODE table, say 1-5 million, which is well within SQL Server’s capability to manage. 

Again, it is important to note that there are no Indexes on either one of these tables.  Given that the record count will be much greater than the MASTER_JCM_COST_CODE table, proper indexing of these tables will dramatically improve extraction times.  The following fields should be indexed:

• Job
• Section
• Cost_Code
• Accounting_Date
• Transaction_Type