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
Erwin Rommel Y. Felix
Tel. 8410111 loc. 11430
Mobile 09185980706
"Never outshine the Master Chicker"
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
