I cannot do anything about the schedule. It seems that my back is against the wall. Below is my script. Can this still be optimized? Thank you very much. Note: The script below processes millions of records...
DECLARE @tblSubTot table (request_ID varchar(9), request_total money)
INSERT @tblSubTot
SELECT tblSourceUsage.request_id, SUM(tblSourceUsage.cost) AS request_total
FROM dbo.erwinrequest_erwin_tbl tblRequest WITH (NOLOCK) LEFT OUTER JOIN dbo.source_usage_erwin_TBL tblSourceUsage WITH (NOLOCK)
ON tblSourceUsage.request_id = tblRequest.request_id
WHERE tblRequest.request_date >= '9/1/2003' and tblRequest.request_ID <> 0
GROUP BY tblSourceUsage.request_id
DECLARE @tblSubTot2 table (personnel_ID varchar(9), totals money, request_ID varchar(5))
INSERT @tblSubTot2
SELECT tblAssigned.personnel_ID, ((tblTime.hours_worked1 + tblTime.hours_worked2 + tblTime.hours_worked3 + tblTime.hours_worked4
+ tblTime.hours_worked5 + tblTime.hours_worked6 + tblTime.hours_worked7 + tblTime.hours_worked8
+ tblTime.hours_worked9 + tblTime.hours_worked10 + tblTime.hours_worked11 + tblTime.hours_worked12
+ tblTime.hours_worked13 + tblTime.hours_worked14 + tblTime.hours_worked15 + tblTime.hours_worked16) *
tblAssigned.researcher_rate) AS TotalCostUSDollars, tblAssigned.request_ID
FROM dbo.assigned_to_request_erwin_TBL tblAssigned WITH (NOLOCK)
RIGHT OUTER JOIN dbo.time_sheet_erwin_TBL tblTime WITH (NOLOCK)
ON tblAssigned.request_ID = tblTime.request_ID
JOIN dbo.erwinrequest_erwin_tbl tblRequest WITH (NOLOCK)
ON tblTime.request_ID = tblRequest.request_ID
WHERE tblAssigned.personnel_ID = tblTime.personnel_ID
AND tblAssigned.personnel_ID <> ''
AND tblRequest.request_date >= '9/1/2003'
INSERT @tblSubTot2
SELECT tblRequest.originalRep_ID, ((tblTime.hours_worked1 + tblTime.hours_worked2 + tblTime.hours_worked3 + tblTime.hours_worked4
+ tblTime.hours_worked5 + tblTime.hours_worked6 + tblTime.hours_worked7 + tblTime.hours_worked8
+ tblTime.hours_worked9 + tblTime.hours_worked10 + tblTime.hours_worked11 + tblTime.hours_worked12
+ tblTime.hours_worked13 + tblTime.hours_worked14 + tblTime.hours_worked15 + tblTime.hours_worked16) *
tblRequest.originalRep_rate) AS TotalCostUSDollars, tblRequest.request_ID
FROM erwinRequest_erwin_TBL tblRequest WITH (NOLOCK) RIGHT OUTER JOIN dbo.time_sheet_erwin_TBL tblTime WITH (NOLOCK)
ON tblRequest.request_ID = tblTime.request_ID
JOIN dbo.erwinrequest_erwin_tbl WITH (NOLOCK)
ON tblTime.request_ID = tblRequest.request_ID
WHERE tblRequest.originalRep_ID = tblTime.personnel_ID AND personnel_ID <> ''
AND tblRequest.request_date >= '9/1/2003'
AND NOT EXISTS (SELECT tblSubTot2.personnel_ID
FROM @tblSubTot2 tblSubTot2
WHERE tblRequest.originalRep_ID = tblSubTot2.personnel_ID )
DECLARE @tblSubTot3 table (totals money, request_ID varchar(5))
INSERT @tblSubTot3
SELECT SUM(totals), request_ID
FROM @tblSubTot2
GROUP BY request_ID
SELECT LTRIM(RTRIM(b.customer_fname)) + ' ' +
LTRIM(RTRIM(b.customer_initial)) + ' ' +
LTRIM(RTRIM(b.customer_lname)) as 'CUSTOMER',
a.request_id as 'REQUEST ID',
LTRIM(RTRIM(CAST(DATEPART(mm, a.request_date) AS char(2)))) + '/' +
LTRIM(RTRIM(CAST(DATEPART(dd, a.request_date) AS char(2))))+ '/' +
LTRIM(RTRIM(CAST(DATEPART(yyyy, a.request_date)AS char(4)))) AS 'REQUEST DATE',
b.level_name as 'CUSTOMER LEVEL',
LTRIM(RTRIM(c.requestedfor_fname)) + ' ' +
LTRIM(RTRIM(c.requestedfor_initial)) + ' ' +
LTRIM(RTRIM(c.requestedfor_lname)) as 'REQUESTED FOR',
c.level_name as 'REQUESTED FOR LEVEL',
d.first_name + ' ' + d.middle_initial + ' ' + d.last_name as 'RESEARCHER',
e.JobCDDescr as 'population-CUSTOMER LEVEL',
e.GeographicUnitDescription as 'population-CUSTOMER GEOGRAPHY',
e.SESLevel2Description as 'population - OPERATING GROUP',
e.CompetencyGroupDescription as 'population - CAPABILITY GROUP',
e.BusinessOrgDescription as 'population - WORKFORCE',
a.chargeable_time_spent_total AS 'HOURS BILLED',
CAST (ROUND(i.totals,0) AS FLOAT) *
CAST (ROUND(f.conversion_factor,0) AS FLOAT) AS 'COST IN LOCAL CURRENCY',
g.currency_name AS 'CURRENCY TYPE',
h.request_total AS 'CONTENT CHARGES'
FROM erwinRequest_erwin_TBL a WITH (NOLOCK) JOIN customer_erwin_TBL b WITH (NOLOCK) ON a.customer_ID = b.customer_ID
JOIN requested_for_erwin_TBL c WITH (NOLOCK) ON a.requestedfor_ID = c.requestedfor_ID
LEFT JOIN person_KRIS_TBL d WITH (NOLOCK) ON a.originalRep_ID = d.personnel_ID
LEFT JOIN population e WITH (NOLOCK) ON e.personnelnumber = b.personnel_id
LEFT JOIN office_KRIS_TBL f WITH (NOLOCK) ON a.office_ID = f.office_ID
LEFT JOIN currency_KRIS_TBL g WITH (NOLOCK) ON f.currency_ID = g.currency_ID
LEFT JOIN @tblSubTot h ON a.request_ID = h.request_ID
LEFT JOIN @tblSubTot3 i ON a.request_ID = i.request_ID
WHERE a.request_date >= '9/1/2003' and a.request_ID <> ''
ORDER BY a.request_ID, a.request_date