2007-08-15

SQL Trickery Needed: ERP Data Extraction

I need to write a new project status report that shows Effort, Revenue, Cost and the like. The twist is that the Project Managers would like to see this information not just for the Project To-Date, but also for the last period for which there was activity.

We use BST Enterprise as our ERP, which has a table PrjTranSummary that has all the information I need. Now I just need some clever SQL to get the information out. This is a classic UNION example where I'm going to create a Summary Record for each project with PTD information and a Last Occurance Record with the information from the last month there was activity.

The table looks kind of like this (I've changed the names in case BST gets testy):

 -----------------------------------------------------
| prj_code | accounting_period | effort    |  cost    |
|==========|===================|===========|==========|
| F7940001 | 200706            |   3465.34 |  1159.23 |
| C5265007 | 200703            | 203465.75 | 10159.00 |
| F7940001 | 200707            |   1843.00 |   326.50 |
| F7940001 | 200707            |  16470.50 | 11325.00 |
 -----------------------------------------------------

There is a record for each project for each month during which that project has had activity. So the only hard part is getting a query that will select for each project only the newest record.

This fits the bill. It is inefficient though. I'm sure that with the right index built, it could be much more efficient.

SELECT *
FROM dbo.PrjTranSummary pts1
WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.PrjTranSummary pts2
        WHERE pts2.prj_code= pts1.prj_code
            AND pts2.accounting_period> pts1.accounting_period
    )

No comments: