all_projects sql script

Drop Table Projects ;Drop Table Bateman ;Drop table task_codes ;Drop table Week_End ;Drop Table Bateman_Sum ;Drop Table Tasks_1065 ;Drop Table Project_Tasks ;Drop Table Departments ;Drop Table Yearly ;Drop Table Prev_Yearly ;Drop Table Sum_Prev_Year ;Drop Table Yearly_Projects ;Drop Table Expenses ;Drop Table Monthly_Projects ;Drop Table Project_Rates ;Drop Table Work_Desc ;Drop Table Demo_Names ;

// Set Month start date ; PARAMETER_SET prmStr_1 = #01/26/2013# ; // Set Month end date ; PARAMETER_SET prmStr_2 = #02/22/2013# ;

// Set start of current year parameter ;

PARAMETER_SET prmStr_3 = #12/29/2012# ;

// Set start of previous year parameter ;


// Set end of previous year parameter ;


// Select lnkWork_List.NUMBER AS P_Number , lnkWork_List.DESCRIPTION , lnkWork_List.CLIENT , lnkWork_List.MANAGER INTO Work_Desc From lnkWork_List ;

Select * INTO Projects from lnkBEPL_Projects where lnkBEPL_Projects.UOM = "HOURS" AND lnkBEPL_Projects.ITEM_DATE BETWEEN prmStr_1 AND prmStr_2 ;

// Select * Into Demo_Names from lnkEmployees_Demo ;

// Update Projects , Demo_Names Set Projects.EMPLOYEE = Demo_Names.EmpNameNew Where Projects.EMPID = Demo_Names.EMPID ;

// Select * INTO Expenses from lnkBEPL_Expenses where lnkBEPL_Expenses.ITEM_DATE > #12/30/2011# AND lnkBEPL_Expenses.PRJ_B <> 0.00 ;

// Alter Table Expenses ADD Column GL_Period Date ;

// Update Expenses SET Expenses.GL_Period = #01/01/2013# where Expenses.ITEM_DATE Between #12/29/2012# AND #01/25/2013# ;// Update Expenses SET Expenses.GL_Period = #02/01/2013# where Expenses.ITEM_DATE Between #01/26/2013# AND #02/22/2013# ;// Update Expenses SET Expenses.GL_Period = #03/01/2013# where Expenses.ITEM_DATE Between #02/23/2013# AND #03/29/2013# ;// Update Expenses SET Expenses.GL_Period = #04/01/2013# where Expenses.ITEM_DATE Between #03/30/2013# AND #04/26/2013# ;// Update Expenses SET Expenses.GL_Period = #05/01/2013# where Expenses.ITEM_DATE Between #04/27/2013# AND #05/31/2013# ;// Update Expenses SET Expenses.GL_Period = #06/01/2013# where Expenses.ITEM_DATE Between #06/01/2013# AND #06/28/2013# ;// Update Expenses SET Expenses.GL_Period = #07/01/2013# where Expenses.ITEM_DATE Between #06/29/2013# AND #07/26/2013# ;// Update Expenses SET Expenses.GL_Period = #08/01/2013# where Expenses.ITEM_DATE Between #07/27/2013# AND #08/30/2013# ;// Update Expenses SET Expenses.GL_Period = #09/01/2013# where Expenses.ITEM_DATE Between #08/31/2013# AND #09/27/2013# ;// Update Expenses SET Expenses.GL_Period = #10/01/2013# where Expenses.ITEM_DATE Between #09/28/2013# AND #10/25/2013# ;// Update Expenses SET Expenses.GL_Period = #11/01/2013# where Expenses.ITEM_DATE Between #10/26/2013# AND #11/29/2013# ;// Update Expenses SET Expenses.GL_Period = #12/01/2013# where Expenses.ITEM_DATE Between #11/30/2013# AND #12/27/2013# ;

Alter Table Projects ADD COLUMN Date_Diff Integer ;Update Projects Set Projects.Date_Diff = (( Projects.ITEM_DATE - prmStr_1 ) mod 35 ) ;

Select lnkBEPL_Projects.PROJECT , lnkBEPL_Projects.TASK , lnkBEPL_Projects.EXPEND_TYPE , lnkBEPL_Projects.ITEM_DATE , lnkBEPL_Projects.EMPLOYEE , lnkBEPL_Projects.EMPID , lnkBEPL_Projects.QUANTITY , lnkBEPL_Projects.PROVIDER_GL , lnkBEPL_Projects.PRJ_B , lnkBEPL_Projects.TRANS_SOURCE , lnkBEPL_Projects.FILE_DATE , lnkBEPL_Projects.ORIG_TRANS_REF, lnkBEPL_Projects.BATCH INTO yearly from lnkBEPL_Projects where lnkBEPL_Projects.ITEM_DATE >= prmStr_3 AND lnkBEPL_Projects.UOM = "HOURS" AND lnkBEPL_Projects.PROJECT NOT LIKE "1065*" AND lnkBEPL_Projects.PROJECT NOT IN ("M6043.B","M6044.B","BAB702.B","D1507","M6021.A","M6031","M6037","M6052","M6059","M6061","M6062","M6064","M6063.B","M6065","M6067.A","M6069","M6072.A","M6076.A","M6077.A","M6078.A","M6080.A","M6079.A","M6082.A","M6085.A","M6086.A") ;

Select Distinct yearly.PROJECT INTO Yearly_Projects From yearly ;

Alter Table Yearly_Projects ADD Column Description TEXT (20) ;Alter Table Yearly_Projects ADD Column CLIENT TEXT (20) ;Alter Table Yearly_Projects ADD Column MANAGER TEXT (20) ;

Update Yearly_Projects Set Yearly_Projects.Description = "Unknown Name" ; Update Yearly_Projects Set Yearly_Projects.CLIENT = "Unknown Name" ;Update Yearly_Projects Set Yearly_Projects.MANAGER = "Unknown Name" ;

// Insert Into Work_Desc Select Yearly_Projects.PROJECT AS P_NUMBER , Yearly_Projects.Description , Yearly_Projects.CLIENT , Yearly_Projects.MANAGER From Yearly_Projects Where Yearly_Projects.PROJECT NOT IN ( Select Work_Desc.P_NUMBER AS Project From Work_Desc ) ;

// Select lnkBEPL_Projects.PROJECT , lnkBEPL_Projects.TASK , lnkBEPL_Projects.EXPEND_TYPE , lnkBEPL_Projects.ITEM_DATE , lnkBEPL_Projects.EMPLOYEE , lnkBEPL_Projects.EMPID , lnkBEPL_Projects.QUANTITY , lnkBEPL_Projects.PROVIDER_GL , lnkBEPL_Projects.PRJ_B , lnkBEPL_Projects.TRANS_SOURCE INTO Prev_Yearly from lnkBEPL_Projects where lnkBEPL_Projects.ITEM_DATE Between prmStr_4 AND prmStr_5 AND lnkBEPL_Projects.UOM = "HOURS" AND lnkBEPL_Projects.PROJECT IN ( Select Yearly_Projects.Project From Yearly_Projects ) ;

// Alter Table Prev_Yearly Add Column WEEK_END Date ;// Alter Table Prev_Yearly Add Column RES_DESC TEXT (30) ;// Alter Table Prev_Yearly Add Column DEPT TEXT (5) ;

// Update Prev_Yearly SET Prev_Yearly.WEEK_END = #12/28/2012 0:00:00 AM# ; // Update Prev_Yearly SET Prev_Yearly.RES_DESC = "Not Applicable" ;// Update Prev_Yearly SET Prev_Yearly.DEPT = "NA" ;// Update Prev_Yearly SET Prev_Yearly.PROVIDER_GL = "Dec-12" ;

// Update Prev_Yearly , Demo_Names Set Prev_Yearly.EMPLOYEE = Demo_Names.EmpNameNew Where Prev_Yearly.EMPID = Demo_Names.EMPID ;

// Select Prev_Yearly.PROJECT , Prev_Yearly.TASK , Prev_Yearly.EXPEND_TYPE , Prev_Yearly.EMPID , Prev_Yearly.PRJ_B , SUM( Prev_Yearly.QUANTITY ) AS Hours INTO Sum_Prev_Year From Prev_Yearly Group By Prev_Yearly.PROJECT , Prev_Yearly.TASK , Prev_Yearly.EXPEND_TYPE , Prev_Yearly.EMPID Order By Prev_Yearly.PROJECT ;

// Delete * from yearly where yearly.PROJECT = "1065 DEPTS" ;

Select * into task_codes from lnkactivity_codes ;

Select * INTO Tasks_1065 from lnkTASKS ;

Select * INTO Departments from lnkDepartments ;

// Update Prev_Yearly , Departments SET Prev_Yearly.RES_DESC = Departments.POSITION Where Prev_Yearly.EXPEND_TYPE = Departments.RESOURCE ;

Select lnkTASKS.PROJECT As Job , lnkTASKS.TASK_NAME As Extra , lnkTASKS.TASK_DESC AS Description INTO Project_Tasks from lnkTASKS ;

Alter Table Projects ADD COLUMN Week_End Date ;Alter Table Projects ADD COLUMN Week_NUM TEXT (1) ;

Alter Table Projects ADD COLUMN USC Integer ;Alter Table Projects ADD COLUMN Task_Desc TEXT (40) ;Alter Table Projects ADD COLUMN Location TEXT (12) ;

Update Projects SET Projects.Location = "Unknown" ;

Alter Table yearly ADD Column WEEK_END Date ;Alter Table yearly ADD Column RES_DESC Text (30) ;Alter Table yearly ADD Column DEPT TEXT (5) ;Alter Table yearly ADD Column Task_Desc TEXT (40) ;Alter Table yearly ADD Column RATE Float ;Alter Table yearly ADD Column SELL Float ;// Alter Table yearly ADD Column Prev_Year Float ;

Update yearly Set yearly.WEEK_END = yearly.ITEM_DATE + ( ( prmStr_2 - yearly.ITEM_DATE) mod 7) ;Update yearly , Departments SET yearly.Res_Desc = Departments.POSITION Where yearly.EXPEND_TYPE = Departments.RESOURCE ;Update yearly , Departments SET yearly.DEPT = Departments.DEPARTMENT Where yearly.EMPID = Departments.EMP_NUM AND yearly.EXPEND_TYPE = Departments.RESOURCE ;

// Update yearly , Sum_Prev_Year SET yearly.Prev_Year = Sum_Prev_Year.Hours Where yearly.PROJECT = Sum_Prev_Year.PROJECT AND yearly.TASK = Sum_Prev_Year.TASK AND yearly.EXPEND_TYPE = Sum_Prev_Year.EXPEND_TYPE AND yearly.EMPID = Sum_Prev_Year.EMPID ;

// Insert INTO yearly Select Prev_Yearly.PROJECT , Prev_Yearly.TASK , Prev_Yearly.EXPEND_TYPE , Prev_Yearly.ITEM_DATE , Prev_Yearly.EMPLOYEE , Prev_Yearly.EMPID , Prev_Yearly.QUANTITY , Prev_Yearly.PROVIDER_GL , Prev_Yearly.PRJ_B , Prev_Yearly.TRANS_SOURCE , Prev_Yearly.WEEK_END , Prev_Yearly.RES_DESC , Prev_Yearly.DEPT From Prev_Yearly ;

Update yearly , Tasks_1065 SET yearly.Task_Desc = Tasks_1065.TASK_DESC Where yearly.TASK = Tasks_1065.TASK_NAME AND yearly.PROJECT = Tasks_1065.PROJECT ;

// Update yearly , Project_Rates SET yearly.RATE = Project_Rates.SELL_RATE Where yearly.PROJECT = Project_Rates.PROJECT And yearly.EXPEND_TYPE = Project_Rates.RESOURCE ;

// Update yearly , Departments Set yearly.EMPLOYEE = Departments.NAME Where yearly.EMPID = Departments.EMP_NUM ;

// Update yearly , Demo_Names Set yearly.EMPLOYEE = Demo_Names.EmpNameNew Where yearly.EMPID = Demo_Names.EMPID ;

// Update yearly SET yearly.SELL = yearly.RATE * yearly.QUANTITY Where yearly.PROJECT IN ("M6030","M6023") ;

Update yearly Set yearly.TRANS_SOURCE = "Adjustment" Where yearly.TRANS_SOURCE IS NULL ;

Update Projects Set Projects.USC = 0 ; Update Projects , Tasks_1065 SET Projects.Task_Desc = Tasks_1065.TASK_DESC Where Projects.TASK = Tasks_1065.TASK_NAME AND Projects.PROJECT = Tasks_1065.PROJECT ;

// Update Projects SET Projects.USC = instr( Projects.TASK , "_") ;// Update Projects SET Projects.USC = instr( Projects.TASK , "-") ;// Update Projects SET Projects.TASK = Right( Projects.TASK , ( Len( Projects.TASK ) - Projects.USC) );

Delete * from Projects Where Projects.PROJECT = "1065 DEPTS" AND Projects.TASK LIKE "L*" ;

Update Projects Set Projects.Week_End = Projects.ITEM_DATE + ( 6 - ( Projects.Date_Diff Mod 7 )) ;

Update Projects , Departments SET Projects.Location = Departments.LOCATION Where Projects.EMPID = Departments.EMP_NUM ;

Update Projects SET Projects.Week_NUM = "5" where Projects.Date_Diff BETWEEN 28 AND 34 ;Update Projects SET Projects.Week_NUM = "4" where Projects.Date_Diff BETWEEN 21 AND 27 ;Update Projects SET Projects.Week_NUM = "3" where Projects.Date_Diff BETWEEN 14 AND 20 ;Update Projects SET Projects.Week_NUM = "2" where Projects.Date_Diff BETWEEN 7 AND 13 ;Update Projects SET Projects.Week_NUM = "1" where Projects.Date_Diff BETWEEN 0 AND 6 ;

Select * into Bateman from Projects Where Projects.PROJECT NOT IN ("1065 DEPTS") ;

// Alter Table Bateman ADD Column USC integer;Alter Table Bateman ADD Column WEEK_END_DATE Date ;Alter Table Bateman ADD Column SATHRS Float ;Alter Table Bateman ADD Column SUNHRS Float ;Alter Table Bateman ADD Column MONHRS Float ;Alter Table Bateman ADD Column TUEHRS Float ;Alter Table Bateman ADD Column WEDHRS Float ;Alter Table Bateman ADD Column THUHRS Float ;Alter Table Bateman ADD Column FRIHRS Float ;Alter Table Bateman ADD Column STTOT Float ;Alter Table Bateman ADD Column Class_Num TEXT (5) ;Alter Table Bateman ADD Column Dept TEXT (5) ;Alter Table Bateman ADD Column CLASSDESCRIP TEXT (50) ;Alter Table Bateman ADD COLUMN PAY_RATE FLOAT ;// Alter Table Bateman ADD COLUMN Task_Desc TEXT (50) ;

// Update Bateman , Week_End SET Bateman.WEEK_END_DATE = Week_End.ITEM_DATE ;

Update Bateman SET Bateman.USC = 0 ; Update Bateman SET Bateman.Dept = "UKN" ;

Update Bateman , task_codes SET Bateman.Task_Desc = task_codes.DESCRIPTION Where Bateman.TASK = task_codes.TASK ;

Update Bateman , Tasks_1065 SET Bateman.Task_Desc = Tasks_1065.TASK_DESC Where Bateman.TASK = Tasks_1065.TASK_NAME AND Bateman.PROJECT = Tasks_1065.PROJECT ;

Update Bateman SET Bateman.SATHRS = 0.00 ;Update Bateman SET Bateman.SUNHRS = 0.00 ;Update Bateman SET Bateman.MONHRS = 0.00 ;Update Bateman SET Bateman.TUEHRS = 0.00 ;Update Bateman SET Bateman.WEDHRS = 0.00 ;Update Bateman SET Bateman.THUHRS = 0.00 ;Update Bateman SET Bateman.FRIHRS = 0.00 ;

Update Bateman SET Bateman.USC = instr( Bateman.TASK , "_") ;Update Bateman SET Bateman.USC = Len( Bateman.TASK ) + 1 where Bateman.USC = 0 ; Update Bateman SET Bateman.Dept = Mid( Bateman.TASK , Bateman.USC + 1 , Len( Bateman.TASK ) ) ; Update Bateman SET Bateman.TASK = Left( Bateman.TASK , Bateman.USC - 1 ) ;

Update Bateman , Project_Tasks SET Bateman.Task_Desc = Project_Tasks.Description WHERE Bateman.PROJECT = Project_Tasks.Job AND Bateman.TASK = Project_Tasks.Extra ;

Update Bateman , Departments SET Bateman.CLASSDESCRIP = Departments.POSITION Where Bateman.EMPID = Departments.EMP_NUM ;

// Update Bateman , lnkPRS_SYSTEM__CERTIFIED_CLASS SET Bateman.CLASSDESCRIP = lnkPRS_SYSTEM__CERTIFIED_CLASS.Description Where Bateman.EXPEND_TYPE = lnkPRS_SYSTEM__CERTIFIED_CLASS.Certified_Class ;

Update Bateman SET Bateman.FRIHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 0 ;

Update Bateman SET Bateman.THUHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 1 ;

Update Bateman SET Bateman.WEDHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 2 ;

Update Bateman SET Bateman.TUEHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 3 ;

Update Bateman SET Bateman.MONHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 4 ;

Update Bateman SET Bateman.SUNHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 5 ;

Update Bateman SET Bateman.SATHRS = Bateman.QUANTITY WHERE ( Bateman.Week_End - Bateman.ITEM_DATE ) mod 7 = 6 ;

Update Bateman SET Bateman.STTOT = Bateman.SATHRS + Bateman.SUNHRS + Bateman.MONHRS + Bateman.TUEHRS + Bateman.WEDHRS + Bateman.THUHRS + Bateman.FRIHRS ;

Select Bateman.PROJECT , Bateman.TASK , Bateman.Task_Desc , Bateman.EXPEND_TYPE , Sum( Bateman.SATHRS ) AS SATHRS , SUM( Bateman.SUNHRS ) AS SUNHRS , SUM ( Bateman.MONHRS ) AS MONHRS , SUM ( Bateman.TUEHRS ) AS TUEHRS , SUM ( Bateman.WEDHRS ) AS WEDHRS , SUM ( Bateman.THUHRS ) AS THUHRS , SUM ( Bateman.FRIHRS ) AS FRIHRS , SUM ( Bateman.STTOT ) AS STTOT , Bateman.EMPLOYEE , Bateman.CLASSDESCRIP , Bateman.Location , Bateman.Week_NUM , Bateman.Week_End INTO Bateman_Sum from Bateman GROUP BY Bateman.PROJECT , Bateman.TASK , Bateman.Task_Desc , Bateman.Dept , Bateman.EMPLOYEE , Bateman.EXPEND_TYPE , Bateman.CLASSDESCRIP , Bateman.Location , Bateman.Week_NUM , Bateman.Week_End ;

Update Projects SET Projects.USC = instr( Projects.TASK , "_") ;Update Projects SET Projects.USC = instr( Projects.TASK , "-") ;Update Projects SET Projects.TASK = Right( Projects.TASK , ( Len( Projects.TASK ) - Projects.USC) );

Delete * from Projects Where Projects.PROJECT = "1065 DEPTS" AND Projects.TASK LIKE "L*" ;

// Select Distinct yearly.PROJECT INTO Monthly_Projects From yearly Where yearly.PROJECT IN ("M6001","M6004","M6005","M6006","M6009","M6010","M6011","M6012","M6013","M6015","M6016","M6022","M6023","M6026","M6028","M6030","M6031","M6032","M6033","M6034","M6035","M6036","M6037","M6038","M6044","M6044.B","M6050","M6053","M60903") Order By yearly.PROJECT ;

// RPT_GRP_START Monthly_Projects , Monthly_Projects.PROJECT ;

// RPT_GRP_START yearly , yearly.PROJECT ;

// export data to spreadsheet ;

DDE Excel C:\SQL_Test\Spreadsheets\Projects_All.xls ;

DDE Excel_Sheet Hours ;

DDE Add_Header 1 , Project , Week 1 , Week 2 , Week 3 , Week 4 , Week 5 , , Totals , ;

DDE Total_Rows Total_Rows_1, 2 , 6 ;

Transform SUM ( Projects.QUANTITY ) as hours

Select Projects.PROJECT from Projects Group By Projects.PROJECT PIVOT Projects.Week_NUM IN ("1","2","3","4","5") ;

DDE Insert_Row 1 ;

DDE Sum_Rows 1 , 2 , 8 , Total Hours , ;

DDE Excel_Sheet Cost ;

DDE Add_Header 1 , Project , Week 1 , Week 2 , Week 3 , Week 4 , Week 5 , , Totals , ;

DDE Total_Rows Total_Rows_1, 2 , 6 ;

Transform SUM ( Projects.PRJ_B ) as costSelect Projects.PROJECT from Projects Group By Projects.PROJECT PIVOT Projects.Week_NUM IN ("1","2","3","4","5") ;

DDE Insert_Row 1 ;

DDE Sum_Rows 1 , 2 , 8 , Total Cost , ;

// DDE Excel_Sheet adjustments ;

// Select * from Projects where Projects.TRANS_SOURCE IS NULL AND Projects.PROJECT IN ("1619","1656","1686","1690","1692","1706","1711","1712","1713","1714","1716","1717");

// Select * from Projects ;

DDE Excel_Sheet Week1 ;

DDE Add_Header 1, Project , Employee Name , Type , Classification , Task , Task Description , Saturday , Sunday , Monday , Tuesday , Wednesday , Thursday , Friday , Total Hours , ;

// Get Current Week Ending for Report ;

Select TOP 1 " Project Manhours Week Ending - " & format( Bateman_Sum.Week_End ,"dd-mmm-yyyy") & " - for review " from Bateman_Sum Where Bateman_Sum.Week_NUM = "1" ;

DDE Insert_Row 1 ;

// DDE Change_Line 1, 1 ;

DDE Sum_On_Change 1, 7, 14, Total for Project , ;

Select Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.EXPEND_TYPE , Bateman_Sum.CLASSDESCRIP ,

Bateman_Sum.TASK , Bateman_Sum.Task_Desc , Bateman_Sum.SATHRS , Bateman_Sum.SUNHRS , Bateman_Sum.MONHRS , Bateman_Sum.TUEHRS , Bateman_Sum.WEDHRS , Bateman_Sum.THUHRS , Bateman_Sum.FRIHRS , Bateman_Sum.STTOT from Bateman_Sum Where Bateman_Sum.Week_NUM = "1" AND Bateman_Sum.PROJECT NOT LIKE "9*" Order by Bateman_Sum.PROJECT , Bateman_Sum.EMPLOYEE , Bateman_Sum.TASK ;

DDE Insert_Row 2 ;

DDE Total_Sum_Change 7, 14, Total Hours for all Projects , ;

DDE Excel_Sheet Week2 ;

DDE Add_Header 1, Project , Employee Name , Type , Classification , Task , Task Description , Saturday , Sunday , Monday , Tuesday , Wednesday , Thursday , Friday , Total Hours , ;


Select TOP 1 "Project Manhours Week Ending - " & format( Bateman_Sum.Week_End ,"dd-mmm-yyyy") & " - for review " from Bateman_Sum Where Bateman_Sum.Week_NUM = "2" ;

DDE Insert_Row 1 ;

DDE Sum_On_Change 1, 7, 14, Total for Project , ;

Select Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.EXPEND_TYPE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc , Bateman_Sum.SATHRS , Bateman_Sum.SUNHRS , Bateman_Sum.MONHRS , Bateman_Sum.TUEHRS , Bateman_Sum.WEDHRS , Bateman_Sum.THUHRS , Bateman_Sum.FRIHRS , Bateman_Sum.STTOT from Bateman_Sum Where Bateman_Sum.Week_NUM = "2" order by Bateman_Sum.PROJECT , Bateman_Sum.EMPLOYEE , Bateman_Sum.TASK ;

DDE Insert_Row 2 ;

DDE Total_Sum_Change 7, 14, Total Hours for all Projects , ;

// DDE Excel_Sheet Week3 ;

// DDE Add_Header 1, Project , Employee Name , Type , Classification , Task , Task Description , Saturday , Sunday , Monday , Tuesday , Wednesday , Thursday , Friday , Total Hours , ;


// Select TOP 1 "Project Manhours Week Ending - " & format( Bateman_Sum.Week_End ,"dd-mmm-yyyy") & " - for review " from Bateman_Sum Where Bateman_Sum.Week_NUM = "3" ;

// DDE Insert_Row 1 ;

// DDE Sum_On_Change 1, 7, 14, Total for Project , ;

// Select Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.EXPEND_TYPE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc , Bateman_Sum.SATHRS , Bateman_Sum.SUNHRS , Bateman_Sum.MONHRS , Bateman_Sum.TUEHRS , Bateman_Sum.WEDHRS , Bateman_Sum.THUHRS , Bateman_Sum.FRIHRS , Bateman_Sum.STTOT from Bateman_Sum Where Bateman_Sum.Week_NUM = "3" order by Bateman_Sum.PROJECT , Bateman_Sum.EMPLOYEE , Bateman_Sum.TASK ;


// DDE Total_Sum_Change 7, 14, Total Hours for all Projects , ;



















// DDE Excel_Sheet charges ;

// DDE Add_Header 1, Project , EMPID , Employee , Task , Week 1 , Week 2 , Week 3 , Week 4 , Week 5 , , Total , ;


// DDE Total_Rows Total_Rows_1, 5 , 9 ;

// Transform SUM( Projects.PRJ_B ) AS CostSelect Projects.PROJECT , Projects.EMPID , Projects.EMPLOYEE , Projects.TASK from Projects Where Projects.PROJECT NOT LIKE "1065*" AND Projects.PROJECT NOT LIKE "9*" Group By Projects.PROJECT , Projects.EMPID , Projects.EMPLOYEE , Projects.TASK Order By Projects.PROJECT , Projects.EMPLOYEE Pivot Projects.Week_End ;

// DDE Insert_Row 1;

// DDE Sum_Rows 1 , 5 , 11 , Total Cost ;

// DDE Excel_Sheet Location_Summary ;

// DDE Add_Header 1, Office , Project , Employee Name , Classification , Task , Task Description , WEEK_1 , WEEK_2 , WEEK_3 , WEEK_4 , WEEK_5, ;


// Select TOP 1 "Project Manhours for Month - " & format( Bateman_Sum.Week_End ,"mmm_yyyy") & " - for review " from Bateman_Sum Where Bateman_Sum.Week_NUM = "1" ;


// DDE Sum_On_Change 1, 7, 11 , Total for Office , ;

// Transform SUM ( Bateman_Sum.STTOT ) AS TotalSelect Bateman_Sum.Location , Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc from Bateman_Sum Group By Bateman_Sum.Location , Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc Order by Bateman_Sum.Location , Bateman_Sum.PROJECT , Bateman_Sum.EMPLOYEE , Bateman_Sum.TASK PIVOT Bateman_Sum.Week_NUM IN ("1","2","3","4","5") ;


// DDE Total_Sum_Change 7, 11 , Total Hours for all Offices , ;

// DDE Excel_Sheet Project_Summary ;

// DDE Add_Header 1, Project , Employee Name , Classification , Task , Task Description , WEEK_1 , WEEK_2 , WEEK_3 , WEEK_4 , WEEK_5, , Total , ;


Select TOP 1 "Project Manhours for Month - " & format( Bateman_Sum.Week_End ,"mmm_yyyy") & " - for review " from Bateman_Sum Where Bateman_Sum.Week_NUM = "1" ;


// DDE Sum_On_Change 1, 6, 12 , Total for Project , ;


// Transform SUM ( Bateman_Sum.STTOT ) AS TotalSelect Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc

from Bateman_Sum Group By Bateman_Sum.PROJECT, Bateman_Sum.EMPLOYEE , Bateman_Sum.CLASSDESCRIP , Bateman_Sum.TASK , Bateman_Sum.Task_Desc Order by Bateman_Sum.PROJECT , Bateman_Sum.EMPLOYEE , Bateman_Sum.TASK PIVOT Bateman_Sum.Week_NUM IN ("1","2","3","4","5") ;


// DDE Total_Sum_Change 6, 12 , Total Hours for all Projects , ;

// totals to date ;

// DDE Excel_Sheet Year_To_Date ;

// DDE Add_Header 1, Project , Employee , Resource , Task, Description , Prev_Year , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13, Aug_13 , Sep_13, Oct_13, Nov_13, Dec_13 , , Total , , Combined , ;

// DDE Sum_On_Change 1, 6, 22 , Total for Project , ;// DDE Insert_Formula 22, =#6~ + #20~ ;


// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT , yearly.EMPLOYEE , yearly.EXPEND_TYPE , yearly.TASK , yearly.Task_Desc from yearly Where yearly.PROJECT NOT LIKE "9*" Group By yearly.PROJECT , yearly.EMPLOYEE , yearly.EXPEND_TYPE , yearly.TASK , yearly.Task_Desc Order By yearly.PROJECT , yearly.EMPLOYEE Pivot format ( yearly.WEEK_END , "mmmyy") IN ("Dec12","Jan13","Feb13","Mar13","Apr13","May13","Jun13","Jul13","Aug13","Sep13","Oct13","Nov13","Dec13") ;


// DDE Total_Sum_Change 7, 22 , Total To Date for all Projects , ;

// DDE Excel_Sheet Activities ;

// DDE Change_Line 1, 1, ;

// Select Distinct Bateman_Sum.PROJECT , Bateman_Sum.TASK from Bateman_Sum Order By Bateman_Sum.PROJECT , Bateman_Sum.TASK ;

// DDE Excel_Sheet Timesheets ;

// DDE Add_Header 1, Employee , , Project , Task , Week End , Hours , , ;

// DDE Sum_On_Change 2, 6, 6 , Total for Week , ;

// Select Bateman_Sum.EMPLOYEE , Left(Bateman_Sum.EMPLOYEE, 4) & "_" & Bateman_Sum.Week_NUM , Bateman_Sum.PROJECT , Bateman_Sum.TASK , Bateman_Sum.Week_End , Bateman_Sum.STTOT from Bateman_Sum Order By Bateman_Sum.EMPLOYEE , Left(Bateman_Sum.EMPLOYEE, 4) & "_" & Bateman_Sum.Week_NUM , Bateman_Sum.Week_End , Bateman_Sum.PROJECT , Bateman_Sum.TASK ;

// DDE Excel_Sheet Projects_Name ;

// DDE Add_Header 1, Project Number , Description , Client , Manager , ;

// Select Work_Desc.P_Number , Work_Desc.DESCRIPTION , Work_Desc.CLIENT , Work_Desc.MANAGER From Work_Desc Order By Work_Desc.P_Number ;

// DDE Excel_Sheet Cost_To_Date ;

// DDE Add_Header 1, Project , Employee , Task , Prev_Yr , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13 , Aug_13 , Sep_13 , Oct_13 , Nov_13 , Dec_13 , , Total , ;

// DDE Sum_On_Change 1, 4, 18 , Total for Project / Proposal , ;


// Transform SUM ( yearly.PRJ_B ) AS CostSelect yearly.PROJECT , yearly.EMPLOYEE , yearly.TASK from yearly Group By yearly.PROJECT , yearly.EMPLOYEE , yearly.TASK Order By yearly.PROJECT , yearly.EMPLOYEE Pivot format ( yearly.WEEK_END , "mmmyy") IN ("Dec12","Jan13","Feb13","Mar13","Apr13","May13","Jun13","Jul13","Aug13","Sep13","Oct13","Nov13","Dec13") ;


// DDE Total_Sum_Change 4, 18 , Total To Date for Project / Proposals , ;

// DDE Excel_Sheet Year_To_Date_GL ;

// DDE Add_Header 1, Project , Employee , Resource , Task , Prev_Year , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13, Aug_13 , Sep_13, Oct_13 , Nov_13 , Dec_13 , , Total , , Combined , ;

// DDE Sum_On_Change 1, 5, 21 , Total for Project , ;// DDE Insert_Formula 21, =#5~ + #19~ ;


// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT, yearly.EMPLOYEE , yearly.EXPEND_TYPE , yearly.TASK from yearly Group By yearly.PROJECT, yearly.EMPLOYEE , yearly.TASK , yearly.EXPEND_TYPE Order By yearly.PROJECT, yearly.EMPLOYEE Pivot yearly.PROVIDER_GL IN ("Dec-12","Jan-13","Feb-13","Mar-13","Apr-13","May-13","Jun-13","Jul-13","Aug-13","Sep-13","Oct-13","Nov-13","Dec-13") ;



// DDE Excel_Sheet Resources_YTD_GL ;

// DDE Add_Header 1, Project , Resource , Description , Task , Prev_Yr , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13 , Aug_13 , Sep_13 , Oct_13 , Nov_13 , Dec_13 , , Total , , Combined , ;


// DDE Insert_Formula 21, =#5~ + #19~ ;


// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT, yearly.EXPEND_TYPE , yearly.Res_Desc , yearly.TASK from yearly Group By yearly.PROJECT, yearly.EXPEND_TYPE , yearly.Res_Desc , yearly.TASK Order By yearly.PROJECT, yearly.EXPEND_TYPE Pivot yearly.PROVIDER_GL IN ("Dec-12","Jan-13","Feb-13","Mar-13","Apr-13","May-13","Jun-13","Jul-13","Aug-13", "Sep-13","Oct-13","Nov-13","Dec-13") ;



// DDE Excel_Sheet Resources_Year_To_Date ;

// DDE Add_Header 1, Project , Resource , Description , Task , Prev_Yr , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13 , Aug_13 , Sep_13 , Oct_13 , Nov_13 , Dec_13 , , Total , , Combined , ;


// DDE Insert_Formula 21, =#5~ + #19~ ;


// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT, yearly.EXPEND_TYPE , yearly.Res_Desc , yearly.TASK from yearly Group By yearly.PROJECT, yearly.EXPEND_TYPE , yearly.Res_Desc , yearly.TASK Order By yearly.PROJECT, yearly.EXPEND_TYPE Pivot format(yearly.WEEK_END , "mmmyy") IN ("Dec12","Jan13","Feb13","Mar13","Apr13","May13","Jun13","Jul13","Aug13","Sep13","Oct13","Nov13","Dec13") ;



// DDE Excel_Sheet Prj_Dept_YTD ;

// DDE Add_Header 1, Project , Task , Dept , Resource , Prev_Yr , Jan_13 , Feb_13 , Mar_13 , Apr_13 , May_13 , Jun_13 , Jul_13 , Aug_13 , Sep_13 , Oct_13 , Nov_13 , Dec_13 , , Total , ;


// DDE Sum_On_Change 1, 5, 19 , Total for Dept , ;

// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT , yearly.TASK , yearly.DEPT , yearly.EXPEND_TYPE FROM yearly Where yearly.PROJECT NOT LIKE "9*" Group By yearly.DEPT , yearly.PROJECT , yearly.TASK , yearly.EXPEND_TYPE Order By yearly.DEPT , yearly.PROJECT , yearly.TASK Pivot Format ( yearly.WEEK_END , "mmmyy" ) IN ("Dec12","Jan13","Feb13","Mar13","Apr13","May13","Jun13","Jul13","Aug13","Sep13","Oct13","Nov13","Dec13") ;

// DDE Excel_Sheet Adjustments ;

// DDE Add_Header 1 , Project , Task , Resource , Date , Employee , EMPID , Hours , WIP , Source , GL_Period , ;

// DDE Change_Line 1, 1, ;


// Select lnkBEPL_Projects.PROJECT , lnkBEPL_Projects.TASK , lnkBEPL_Projects.EXPEND_TYPE , lnkBEPL_Projects.ITEM_DATE , lnkBEPL_Projects.EMPLOYEE , lnkBEPL_Projects.EMPID , lnkBEPL_Projects.QUANTITY , lnkBEPL_Projects.PRJ_B , " - " & lnkBEPL_Projects.BATCH , lnkBEPL_Projects.PROVIDER_GL , lnkBEPL_Projects.FILE_DATE From lnkBEPL_Projects Where lnkBEPL_Projects.BATCH LIKE "AD*" AND lnkBEPL_Projects.ITEM_DATE > #07/29/2011# AND lnkBEPL_Projects.PROJECT NOT IN ("M6043.B","M6044.B","BAB702.B","D1507","M6021.A","M6031","M6037","M6052","M6059","M6061","M6062","M6064","M6063.B","M6065","M6067.A","M6069","M6072.A","M6076.A","M6077.A","M6078.A","M6080.A","M6079.A","M6082.A","M6085.A","M6086.A") Order By lnkBEPL_Projects.PROJECT , lnkBEPL_Projects.ITEM_DATE , lnkBEPL_Projects.EMPLOYEE ;

// DDE Excel_Sheet Monthly_Hours ;

// DDE Add_Header 1 , Employee , Project & Resource , Description , Task , Description , Prev_Yr , January , February , March , April , May , June , July , August, 02_Sep, 09_Sep , 16_Sep , 23_Sep , 30_Sep , , Total , ;


// DDE Sum_On_Change 2, 6, 21 , Total for Task , ;

// Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.EMPLOYEE , Monthly_Projects.PROJECT & " - " & yearly.TASK , yearly.Task_Desc , yearly.EXPEND_TYPE , yearly.RES_DESC FROM yearly , Monthly_Projects Where yearly.PROJECT = Monthly_Projects.PROJECT Group By Monthly_Projects.PROJECT & " - " & yearly.TASK , yearly.Task_Desc , yearly.EMPLOYEE , yearly.EXPEND_TYPE , yearly.RES_DESC Order By Monthly_Projects.PROJECT & " - " & yearly.TASK , yearly.EMPLOYEE Pivot Format ( yearly.WEEK_END , "ddmmm" ) IN

("31Dec","28Jan","25Feb","25Mar","29Apr","27May","24Jun","29Jul","26Aug","02Sep","09Sep","16Sep","23Sep","30Sep") ;



// DDE Excel_Sheet Expenses ;

// DDE Add_Header 1, Project , Task , EXPND_TYPE , Date , Expenditure , Cost , Description , ;

// DDE Sum_On_Change 1, 6, 6, Total Expenses for Project , ;

// Select Expenses.PROJECT , Expenses.TASK , Expenses.EXPEND_TYPE , Expenses.ITEM_DATE , Expenses.EMPLOYEE , Expenses.PRJ_B , Expenses.COMMENT from Expenses Where Expenses.ITEM_DATE > #06/30/2011# Order By Expenses.PROJECT , Expenses.TASK , Expenses.ITEM_DATE ;

// DDE Excel_Sheet Monthly_Project_Hours ;

// DDE Add_Header 1 , Project , Employee , Task , Description , Resource , Description , Prev_Yr , January , February , March , April , May , June , July , August , September , October, 04_Nov , 11_Nov , 18_Nov , 25_Nov , , Total , ;

// RPT_GRP DDE Total_Rows Total_Rows_1, 7 , 21 ;

// RPT_GRP DDE Sum_On_Change 2, 7, 23 , Total for Task , ;

// RPT_GRP Transform SUM ( yearly.QUANTITY ) AS HoursSelect yearly.PROJECT , yearly.EMPLOYEE , yearly.TASK , yearly.Task_Desc , yearly.EXPEND_TYPE , yearly.RES_DESC FROM yearly Group By yearly.PROJECT , yearly.TASK , yearly.Task_Desc , yearly.EMPLOYEE , yearly.EXPEND_TYPE , yearly.RES_DESC Order By yearly.PROJECT , yearly.TASK , yearly.EMPLOYEE Pivot Format ( yearly.WEEK_END , "ddmmm" ) IN ("31Dec","28Jan","25Feb","25Mar","29Apr","27May","24Jun","29Jul","26Aug","30Sep","28Oct","04Nov","11Nov","18Nov","25Nov") ;

// RPT_GRP DDE Insert_Row 2 ;

// RPT_GRP DDE Total_Sum_Change 7, 17 , Total To Date for Project , ;

// RPT_GRP_END_1 ;

// DDE Excel_Sheet Expenses_GL ;

// DDE Add_Header 1, Project , January , February , March , April , May , June , July , August, September, October, November, December , , Total , ;


// Transform SUM( Expenses.PRJ_B ) As ExpenseSelect Expenses.PROJECT From Expenses Where Expenses.ITEM_DATE > #01/01/2012# Group By Expenses.PROJECT Order BY Expenses.PROJECT Pivot Format ( Expenses.GL_Period , "ddmmm") IN ("01Jan","01Feb","01Mar","01Apr","01May","01Jun","01Jul","01Aug","01Sep","01Oct","01Nov","01Dec") ;


// DDE Sum_Rows 1 , 2 , 15 , Total Expenses , ;

all_projects sql script

Documents