This is procedure code.
The biggest table is prj_detail ( 53,000,000 records)
ALTER PROCEDURE UnitPricingTrans
(
@P_PER_START int,
@P_PER_END int,
@P_CO_CODE varchar(1000),
@P_UNIT_TYPE varchar(1000),
@P_UNIT varchar(1000)
)
AS
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#UnitType')) DROP TABLE #UnitType;
DECLARE @XML XML
SET @XML = '<list><i>' + Replace(@P_UNIT_TYPE, ',', '</i><i>') + '</i></list>'
SELECT x.y.value('.', 'varchar(1000)') AS Value INTO #UnitType FROM @XML.nodes('list/i/text()') AS x(y)
--select Value from #UnitType
--return
select
prj_detail.accounting_period,
prj_detail.co_code,
prj_detail.acct_currency_code,
equip_type.equip_type_code + ' - ' + equip_type.equip_type_name as UnitType,
equip_code.equip_code + ' - ' + equip_code.equip_name as Unit,
prj_info.prj_code + ' - ' + prj_info.prj_name as Project,
equip_code.equip_code + ' - ' + equip_code.equip_name as Equipment,
prj_detail.qty_amt as Quantity,
measure_code.measure_name as Measure,
prj_detail.goal_multiplier as Multiplier,
up_sched.up_sched_name as UPSchedule,
prj_detail.cost_cc AS Cost,
prj_detail.eff_cc AS Effort,
prj_detail.prj_code,
per_end_date
from
equip_type
inner join
equip_code
on equip_type.equip_type_code = equip_code.equip_type_code
inner join
prj_detail
on equip_code.equip_code = prj_detail.equip_code
inner join
doc_type
on prj_detail.sys_doc_type_code = doc_type.sys_doc_type_code
inner join
prj_info
on prj_detail.prj_code = prj_info.prj_code
inner join
unit_code
on prj_detail.prj_detail_code = unit_code.unit_code
inner join
measure_code
on unit_code.measure_code = measure_code.measure_code
inner join
up_sched_units
on unit_code.unit_code = up_sched_units.unit_code
inner join
up_sched
on up_sched_units.up_sched_code = up_sched.up_sched_code
where
doc_type.sys_doc_type_code = 'UP'
and
prj_detail.co_code IN (@P_CO_CODE)
and
equip_type.equip_type_code IN (select Value from #UnitType)
and
equip_code.equip_code IN (@P_UNIT)
and
accounting_period between @P_PER_START and @P_PER_END