OpenAir ETL Project
 |
| Image via CrunchBase |
This is how I did it.
First we needed to purchase OpenAir's Automated Backup service. This service will export data in text format or MySQL dumps of any or all tables in their database, zip them up and deposit them anywhere via FTP, FTPS, or SFTP. We set up an FTP server for our repository in the DMZ.
Once this bit was done, I needed an installation of MySQL Server and a method to programmatically pull the data from the DMZ, deposit it on a Windows share, unzip and insert the data. Below is a batch file used for this purpose.
@ECHO On
SETLOCAL ENABLEDELAYEDEXPANSION
@SET _LOGFILE="C:\Export\LOGS\OpenAirMysqlImports.txt"
@SET _MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe"
@FOR /F "TOKENS=2,3,4 DELIMS=/ " %%A IN ('DATE /T') DO @(
@SET _MM=%%A
@SET _DD=%%B
@SET _YYYY=%%C
)
cd C:\openair
ECHO ------------------------------------------------------------------------------------------------------------- >> %_LOGFILE%
ECHO -- START %DATE% - %TIME% -- >> %_LOGFILE%
ECHO ------------------------------------------------------------------------------------------------------------- >> %_LOGFILE%
:: //////////////////////////////////////////////
::
:: Download the zip file from our repository.
::
wget --quiet --ftp-user=MyUsername --ftp-password=MyPassword ftp://my.ftp.site/openair/openair_%_YYYY%-%_MM%-%_DD%.zip
SET _ERRORLVL==%ERRORLEVEL%
IF %_ERRORLVL%==1 SET _MSG=WGET Generic error code.
IF %_ERRORLVL%==2 SET _MSG=WGET Parse error—for instance, when parsing command-line options, the ‘.wgetrc’ or ‘.netrc’...
IF %_ERRORLVL%==3 SET _MSG=WGET File I/O error.
IF %_ERRORLVL%==4 SET _MSG=WGET Network failure.
IF %_ERRORLVL%==5 SET _MSG=WGET SSL verification failure.
IF %_ERRORLVL%==6 SET _MSG=WGET Username/password authentication failure.
IF %_ERRORLVL%==7 SET _MSG=WGET Protocol errors.
IF %_ERRORLVL%==8 SET _MSG=WGET Server issued an error response.
IF NOT %_ERRORLVL%==0 ECHO #### WGET ERROR - %_MSG% #### >> %_LOGFILE% & GOTO SENDMAIL
:: //////////////////////////////////////////////
::
:: Delete the file we just downloaded.
::
ECHO user MyUsername> ftpcmd.dat
ECHO MyPassword>> ftpcmd.dat
ECHO bin>> ftpcmd.dat
ECHO cd /openair/>> ftpcmd.dat
ECHO delete openair_%_YYYY%-%_MM%-%_DD%.zip>> ftpcmd.dat
ECHO quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat my.ftp.site
DEL ftpcmd.dat
:: //////////////////////////////////////////////
::
:: Delete the old sql scripts and extract the new
::
DEL /Q MYSQL*.SQL
7z.exe e -y openair_%_YYYY%-%_MM%-%_DD%.zip
SET _ERRORLVL==%ERRORLEVEL%
IF %_ERRORLVL%==1 SET _MSG=7ZIP Warning (Non fatal error(s)). For example, one or more files were locked by some other application, so they were not compressed.
IF %_ERRORLVL%==2 SET _MSG=7ZIP Fatal error
IF %_ERRORLVL%==7 SET _MSG=7ZIP Command line error
IF %_ERRORLVL%==8 SET _MSG=7ZIP Not enough memory for operation
IF %_ERRORLVL%==255 SET _MSG=7ZIP User stopped the process
IF NOT %_ERRORLVL%==0 ECHO #### 7z ERROR - %_MSG% #### >> %_LOGFILE% & GOTO SENDMAIL
:: //////////////////////////////////////////////
::
:: First test for existance of files, and
:: notify us if they don't exist.
DIR /B MYSQL*.SQL
IF NOT %ERRORLEVEL%==0 SET _MSG=No mysql files were found. & ECHO #### MYSQL FILE CHECK ERROR - NO FILES FOUND #### >> %_LOGFILE% & GOTO SENDMAIL
:: //////////////////////////////////////////////
::
:: Insert the OpenAir database information.
DIR /B MYSQL*.SQL | FIND /V "" > FILES.TXT
FOR /F "tokens=1" %%f IN (FILES.TXT) DO (
ECHO -- Inserting %%f >> %_LOGFILE%
gsar "-sNOT NULL" -rNULL -o %%f
%_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < %%f
)
DEL /Q FILES.TXT
DEL /Q OPENAIR_%_YYYY%-%_MM%-%_DD%.ZIP
:: //////////////////////////////////////////////
::
:: Check to see if today is Sun, and insert our weekly
:: update if it is.
@SET _TODAY=%DATE:~0,3%
IF %_TODAY% == Sun %_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < insert.sql
:: //////////////////////////////////////////////
::
:: Create the report table
::
%_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < CreateReport.sql
:: /////////////////////////////////////////////
::
:: Export the PM reports
::
CALL C:\Export\ExportPmReports.cmd
GOTO EOF
:SENDMAIL
:: //////////////////////////////////////////////
::
:: Set the temp file location
SET _TEMPMAIL=%TEMP%\TEMPMAIL.%RANDOM%.TXT
:: //////////////////////////////////////////////
::
:: Echo the basic headers to the temp file
ECHO TO: "Croson, John" ^ > %_TEMPMAIL%
ECHO FROM: "Merge Admin" ^ >> %_TEMPMAIL%
ECHO SUBJECT: OpenAir MySQL Update Failure >> %_TEMPMAIL%
:: //////////////////////////////////////////////
::
:: Echo the blank line that separates the header from the body text
ECHO.>>%_TEMPMAIL%
:: //////////////////////////////////////////////
::
:: Echo the body text to the temp file
ECHO %_MSG% >> %_TEMPMAIL%
ECHO Check %_LOGFILE% for details.>> %_TEMPMAIL%
:: //////////////////////////////////////////////
::
:: Move the temp file to the mail pickup directory
:: adjust this location for your system
MOVE %_TEMPMAIL% C:\INETPUB\MAILROOT\PICKUP
GOTO EOF
:EOF
EXIT
If you noticed a line in my code that references gsar, that bit turns all NOT NULL fields in the MySQL dump files into NULL fields. We use that so our inserts go a little easier when our home grown web service utility pings OpenAir periodically for updates to the booking_by_day table, and are updated in our database. This is a work in progress and if any one has interest, you can post to that effect and I'll see what I can do about getting that up here.
CreateReport.sql is a call to a store procedure that I wrote that rolls all project manager data into one huge report. Some of the data on this report comes from the previous download, and some comes from the historical table I insert data into on Sunday's. See below.
DROP PROCEDURE IF EXISTS openair.CreateReportTable;
CREATE PROCEDURE openair.`CreateReportTable`()
BEGIN
DROP TABLE IF EXISTS tblTempWeeklyReport2;
CREATE TABLE tblTempWeeklyReport2 AS
SELECT P.ID AS "PROJECT_ID",
U.ID AS "USER_ID",
U.NAME AS "USER_NAME",
D.NAME AS "DEPARTMENT_NAME",
C.NAME AS "CLIENT",
P.NAME AS "PROJECT_NAME",
P.CUSTOM_31 AS "PRODUCT_TYPE",
P.CUSTOM_40 AS "REV_REC_TYPE",
P.BUDGET AS "PS_REVENUE_USD",
P.BUDGET_TIME AS "PS_REVENUE_HOURS",
STR_TO_DATE(P.CUSTOM_68, '%Y-%m-%d') AS "PROJECTED_ACTUAL_CLOSED_DATE",
(SELECT STR_TO_DATE(MAX(ENDDATE),'%Y-%m-%d') FROM BOOKING B WHERE PROJECT_ID=P.ID AND USER_ID=U.ID AND B.DELETED != '1') AS "LAST_BOOKING_DATE",
PS.NAME AS "STAGE",
P.NOTES AS "NOTES",
IFNULL(SUM(PT.PLANNED_HOURS),0) AS "PLANNED_HOURS_CURRENT",
(SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') AS "WORKED_TO_DATE",
(SELECT IFNULL(WORKED_HOURS,0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND USER_ID = U.ID AND YEARWEEK(`DATE`) = YEARWEEK(CURDATE()) -1) AS "WORKED_LAST_WEEK",
(SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY WHERE PROJECT_ID=P.ID AND `DATE` >= CURDATE() AND deleted != '1') AS "REMAINING_BOOKED",
(SELECT IFNULL(SUM(PLANNED_HOURS),0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND USER_ID = U.ID AND YEARWEEK(`DATE`) = YEARWEEK(CURDATE()) - 1) AS "PLANNED_HOURS_LAST_WEEK",
(SELECT IFNULL(SUM(HOURS),0) FROM BOOKING_BY_DAY WHERE PROJECT_ID=P.ID AND DELETED != '1' AND `DATE` BETWEEN CURDATE() AND LAST_DAY_OF_QTR(CURDATE())) AS "BOOKED_HOURS_AS_OF_TODAY",
(SELECT IFNULL(SUM(BOOKED_HRS_QTR),0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND `DATE` BETWEEN CURDATE()-7 AND LAST_DAY_OF_QTR(CURDATE())) AS "BOOKED_HOURS_AS_OF_LAST_WEEK",
CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100.00 END AS "CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED",
CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100.00 END AS "EOQ_ESTIMATE_PERC_COMPLETE",
(SELECT IFNULL(PERCENT_COMPLETE,0.00) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND `DATE` = DATE_SUB(LAST_WDOM(LAST_DAY_OF_QTR(CURDATE())), INTERVAL 1 QUARTER)) AS "PREVIOUS_QTR_COMPLETE",
CASE WHEN UPPER(P.CUSTOM_40) LIKE '%DEFERRAL%' THEN 0000000000.00
WHEN UPPER(P.CUSTOM_40) LIKE 'COMPLETED CONT%' THEN 0000000001.00
WHEN UPPER(P.CUSTOM_40) LIKE '\%% COMPLETE%' THEN 0000000002.00
END AS "REVENUE_FORCAST"
FROM PROJECT P
LEFT OUTER JOIN USER U ON U.ID = P.USER_ID
LEFT OUTER JOIN PROJECT_STAGE PS ON PS.ID = P.PROJECT_STAGE_ID
LEFT OUTER JOIN CUSTOMER C ON C.ID = P.CUSTOMER_ID
LEFT OUTER JOIN PROJECT_TASK PT ON PT.PROJECT_ID = P.ID
LEFT OUTER JOIN DEPARTMENT D ON D.ID = U.DEPARTMENT_ID
WHERE PS.ID IN (2,3,4,6,7,9) AND PT.DELETED != 1 AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '' AND ( LEFT(D.NAME,6) IN ('013000', '016000', '016200','016500') OR D.NAME IS NULL )
GROUP BY P.ID
ORDER BY 1 ASC;
CREATE INDEX PID_UID ON TBLTEMPWEEKLYREPORT2 (PROJECT_ID, USER_ID);
---- SET THE WORKED DELTA
---- AND WORKED DELTA PERC
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD WORKED_DELTA DECIMAL(10,2) AFTER WORKED_LAST_WEEK;
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_WORKED_PERC DECIMAL(10,2) AFTER WORKED_DELTA;
UPDATE TBLTEMPWEEKLYREPORT2 SET WORKED_DELTA = IFNULL(WORKED_TO_DATE - WORKED_LAST_WEEK,0);
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_WORKED_PERC = IFNULL(WORKED_DELTA / WORKED_LAST_WEEK, 0) * 100;
---- DELTA_OF_PLANNED_VS_WORKED_BOOKED
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_OF_PLANNED_VS_WORKED_BOOKED DECIMAL(10,2) AFTER REMAINING_BOOKED;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_OF_PLANNED_VS_WORKED_BOOKED = PLANNED_HOURS_CURRENT - WORKED_TO_DATE - REMAINING_BOOKED;
---- DELTA_PERCENT
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT DECIMAL(10,2) AFTER DELTA_OF_PLANNED_VS_WORKED_BOOKED;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT = (DELTA_OF_PLANNED_VS_WORKED_BOOKED / PLANNED_HOURS_CURRENT) * 100;
---- CHANGE_IN_PLANNED_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGE_IN_PLANNED_HOURS DECIMAL(10,2) AFTER PLANNED_HOURS_LAST_WEEK;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGE_IN_PLANNED_HOURS = (PLANNED_HOURS_CURRENT - PLANNED_HOURS_LAST_WEEK);
---- DELTA_PERCENT_1
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT_1 DECIMAL(10,2) AFTER CHANGE_IN_PLANNED_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT_1 = IFNULL(CHANGE_IN_PLANNED_HOURS / PLANNED_HOURS_LAST_WEEK,0) * 100;
---- PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS DECIMAL(10,2) AFTER DELTA_PERCENT_1;
UPDATE TBLTEMPWEEKLYREPORT2 SET PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS = (PLANNED_HOURS_CURRENT - PS_REVENUE_HOURS);
---- CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS DECIMAL(10,2) AFTER PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS = (PLANNED_HOURS_CURRENT - PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS);
---- DELTA_PERCENT_3
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT_3 DECIMAL(10,2) AFTER CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT_3 = IFNULL(CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS / PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS,0) * 100;
---- CHANGED
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGED DECIMAL(10,2) AFTER BOOKED_HOURS_AS_OF_LAST_WEEK;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGED = (BOOKED_HOURS_AS_OF_TODAY - BOOKED_HOURS_AS_OF_LAST_WEEK);
---- PERCENT_CHANGE
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD PERCENT_CHANGE DECIMAL(10,2) AFTER CHANGED;
UPDATE TBLTEMPWEEKLYREPORT2 SET PERCENT_CHANGE = IFNULL(CHANGED / BOOKED_HOURS_AS_OF_LAST_WEEK, 0) * 100;
---- CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED
UPDATE TBLTEMPWEEKLYREPORT2 SET CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED =
CASE WHEN (WORKED_TO_DATE / PLANNED_HOURS_CURRENT) * 100 > 100 THEN 100
WHEN CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = 100 THEN 100
ELSE (WORKED_TO_DATE / PLANNED_HOURS_CURRENT) * 100
END;
UPDATE TBLTEMPWEEKLYREPORT2 SET CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = 0
WHERE CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED IS NULL OR
CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = '';
---- EOQ_ESTIMATE_PERC_COMPLETE
UPDATE TBLTEMPWEEKLYREPORT2 SET EOQ_ESTIMATE_PERC_COMPLETE =
CASE WHEN (WORKED_TO_DATE + BOOKED_HOURS_AS_OF_TODAY) / PLANNED_HOURS_CURRENT * 100 > 100 THEN 100
WHEN UNIX_TIMESTAMP(PROJECTED_ACTUAL_CLOSED_DATE) != 0 THEN
(CASE WHEN PROJECTED_ACTUAL_CLOSED_DATE < LAST_DAY_OF_QTR(CURDATE()) THEN 100 END)
WHEN UNIX_TIMESTAMP(LAST_BOOKING_DATE) != 0 THEN
(CASE WHEN LAST_BOOKING_DATE < LAST_DAY_OF_QTR(CURDATE()) THEN 100 END)
ELSE #WORKED + BOOKED HOURS AS OF TODAY TO END OF QTR / PLANNED HOURS
(WORKED_TO_DATE + BOOKED_HOURS_AS_OF_TODAY) / PLANNED_HOURS_CURRENT * 100
END;
UPDATE TBLTEMPWEEKLYREPORT2 SET EOQ_ESTIMATE_PERC_COMPLETE = 0
WHERE EOQ_ESTIMATE_PERC_COMPLETE IS NULL OR
EOQ_ESTIMATE_PERC_COMPLETE = '';
---- IN_QTR_GAIN
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD IN_QTR_GAIN DECIMAL(10,2) AFTER EOQ_ESTIMATE_PERC_COMPLETE;
UPDATE TBLTEMPWEEKLYREPORT2 SET IN_QTR_GAIN = IFNULL(EOQ_ESTIMATE_PERC_COMPLETE,0.00) - IFNULL(PREVIOUS_QTR_COMPLETE,0.00);
---- REVENUE_FORCAST
UPDATE TBLTEMPWEEKLYREPORT2 SET REVENUE_FORCAST =
CASE WHEN REVENUE_FORCAST = 1.00 AND EOQ_ESTIMATE_PERC_COMPLETE = 100.00 AND PREVIOUS_QTR_COMPLETE < 100.00 THEN PS_REVENUE_USD
WHEN REVENUE_FORCAST = 2.00 THEN IN_QTR_GAIN * PS_REVENUE_USD / 100
END;
---- REALIZATION_RATE
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD REALIZATION_RATE DECIMAL(10,2) AFTER REVENUE_FORCAST;
UPDATE TBLTEMPWEEKLYREPORT2 SET REALIZATION_RATE =
CASE WHEN UPPER(STAGE) = "CLOSED" THEN (PS_REVENUE_USD / WORKED_TO_DATE)
ELSE
(PS_REVENUE_USD / PLANNED_HOURS_CURRENT)
END;
---- Delete the old report table instead of selecting into since columns don't appear to be created correctly
DROP TABLE IF EXISTS TBLWEEKLYREPORT;
---- Rename our new report data
RENAME TABLE TBLTEMPWEEKLYREPORT2 TO TBLWEEKLYREPORT;
END;
Insert.sql that is executed on Sunday's is a call to a query that inserts project management historical data for reporting above. See below. INSERT INTO pm_report_hist ( project_id, user_id, date, planned_hours, remaining_booked, booked_hrs_qtr, worked_hours, percent_complete )
SELECT P.ID,
U.ID,
CURDATE(),
IFNULL(SUM(PT.PLANNED_HOURS),0), -- #### Planned Hours
(SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY
WHERE PROJECT_ID=P.ID AND `DATE` >= CURDATE() AND DELETED != '1'), -- #### Remaining Booked
(SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY
WHERE PROJECT_ID=P.ID AND DELETED != '1' AND `DATE`
BETWEEN CURDATE() AND LAST_DAY_OF_QTR(CURDATE())), -- #### booked hours qtr
(SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = ''), -- #### Worked Hours
CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100
WHEN (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') /
IFNULL(SUM(PT.PLANNED_HOURS),0) > 100 THEN 100
ELSE (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') /
IFNULL(SUM(PT.PLANNED_HOURS),0) * 100 -- #### Percent Complete
END
FROM PROJECT P
LEFT OUTER JOIN USER U ON U.ID = P.USER_ID
LEFT OUTER JOIN PROJECT_TASK PT ON PT.PROJECT_ID = P.ID
LEFT OUTER JOIN PROJECT_STAGE PS ON PS.ID = P.PROJECT_STAGE_ID
LEFT OUTER JOIN DEPARTMENT D ON D.ID = U.DEPARTMENT_ID
WHERE PS.ID IN (2,3,4,6,7,9) AND PT.DELETED != 1 AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '' AND ( LEFT(D.NAME,6) IN ('013000', '016000', '016200','016500') OR D.NAME IS NULL )
GROUP BY P.ID
ORDER BY 1 ASC;
Once this data is populated, an external reporting tool called Clickbase is used to create individual reports from the table that is created by the store procedure, and dump them neatly into a Windows share every day in Excel format. This was quite an effort and represents a number of hours of development time, not to mention meetings with my liaison from finance that specializes in financial reporting. 
Comments
Thanks for this post!