Complicated Query - Cannot setup DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Complicated Query - Cannot setup DTS

Author

Topic

varneym
Starting Member

3 Posts

Posted - 2008-06-25 : 10:11:29

I am trying to setup a DTS for the following query but get this error when trying to setup:

Error Source: Microsoft OLE DB Provider for SQL Server

Error description: Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name '#Operating_Statistics'.

Context: Error calling GetColumnInfo.
Query:
BEGIN

DECLARE
@division VARCHAR(2),
@d_date DATETIME,
@month INTEGER,
@year INTEGER

SELECT
@division = '40',
@d_date = CONVERT(datetime, CONVERT(varchar, GETDATE()-1, 103), 103)

SELECT @month = MONTH(@d_date)
SELECT @year = YEAR(@d_date)

CREATE TABLE #Operating_Statistics(
division CHAR(02) NULL,
site CHAR(3) NULL,
output_date datetime Null,
-- DAILY FIELDS
d_truck_loads DECIMAL(10,2) NULL,
d_primary_production DECIMAL(10,2) NULL,
d_surge_fly_over_adj DECIMAL(10,2) NULL,
d_primary_waste DECIMAL(10,2) NULL,
d_primary_hours_opr DECIMAL(10,2) NULL,
d_primary_hours_sch DECIMAL(10,2) NULL,
d_net_primary_production DECIMAL(10,2) NULL,
d_primary_finished_products DECIMAL(10,2) NULL,
d_surge_stone_sales DECIMAL(10,2) NULL,
d_secondary_production DECIMAL(10,2) NULL,
d_fly_over_adj DECIMAL(10,2) NULL,
d_secondary_waste DECIMAL(10,2) NULL,
d_reprocess_other DECIMAL(10,2) NULL,
d_produced_as_sold DECIMAL(10,2) NULL,
d_control_hours_opr DECIMAL(10,2) NULL,
-- MONTHLY FIELDS
m_truck_loads DECIMAL(10,2) NULL,
m_primary_production DECIMAL(10,2) NULL,
m_surge_fly_over_adj DECIMAL(10,2) NULL,
m_primary_waste DECIMAL(10,2) NULL,
m_primary_hours_opr DECIMAL(10,2) NULL,
m_primary_hours_sch DECIMAL(10,2) NULL,
m_net_primary_production DECIMAL(10,2) NULL,
m_primary_finished_products DECIMAL(10,2) NULL,
m_surge_stone_sales DECIMAL(10,2) NULL,
m_secondary_production DECIMAL(10,2) NULL,
m_fly_over_adj DECIMAL(10,2) NULL,
m_secondary_waste DECIMAL(10,2) NULL,
m_reprocess_other DECIMAL(10,2) NULL,
m_produced_as_sold DECIMAL(10,2) NULL,
m_control_hours_opr DECIMAL(10,2) NULL,
m_capped_production_tons DECIMAL(10,2) NULL,
-- YTD FIELDS
y_truck_loads DECIMAL(10,2) NULL,
y_primary_production DECIMAL(10,2) NULL,
y_surge_fly_over_adj DECIMAL(10,2) NULL,
y_primary_waste DECIMAL(10,2) NULL,
y_primary_hours_sch DECIMAL(10,2) NULL,
y_primary_hours_opr DECIMAL(10,2) NULL,
y_net_primary_production DECIMAL(10,2) NULL,
y_primary_finished_products DECIMAL(10,2) NULL,
y_surge_stone_sales DECIMAL(10,2) NULL,
y_secondary_production DECIMAL(10,2) NULL,
y_fly_over_adj DECIMAL(10,2) NULL,
y_secondary_waste DECIMAL(10,2) NULL,
y_reprocess_other DECIMAL(10,2) NULL,
y_produced_as_sold DECIMAL(10,2) NULL,
y_control_hours_opr DECIMAL(10,2) NULL,
y_capped_production_tons DECIMAL(10,2) NULL,
d_net_primary_finished_products DECIMAL(10,2) NULL,
w_net_primary_finished_products DECIMAL(10,2) NULL,
m_net_primary_finished_products DECIMAL(10,2) NULL,
y_net_primary_finished_products DECIMAL(10,2) NULL,
min_surge DECIMAL(10,2) NULL,
max_surge DECIMAL(10,2) NULL)

INSERT INTO #Operating_Statistics(
division,
site
)
SELECT
div,
dept
FROM
vs_r_dept
WHERE
div = @division AND
pm_code = 'Y'

UPDATE
#Operating_Statistics
SET
--------------------------------------------------------------------------------------------------------------MONTHLY FIELDS --------------------------------------------------------------------------------------------------

d_truck_loads = coalesce((SELECT sum(coalesce(prim_loads,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date), 0),

d_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0))
FROM pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location= #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date), 0),

d_surge_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type= 'S' AND
pms_a_adj_detail.tran_date = @d_date), 0),

d_primary_waste= coalesce((SELECT sum(coalesce(waste_tons,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date), 0),

d_primary_hours_sch = coalesce((SELECT sum(coalesce(prim_hrs_sched,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date), 0),

d_primary_hours_opr = coalesce((SELECT sum(coalesce(prim_hrs_oper,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date ), 0),

d_net_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0) - coalesce(waste_tons,0) )
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
pms_a_primary_prod_header.tran_date = @d_date ), 0) +
coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div= #Operating_Statistics.division AND
pms_a_adj_detail.location= #Operating_Statistics.site AND
pms_a_adj_detail.adj_type= 'S' AND
pms_a_adj_detail.tran_date = @d_date ), 0) ,

d_net_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) - COALESCE(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
pms_a_plant_prod_detail.tran_date = @d_date), 0),

d_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
pms_a_plant_prod_detail.tran_date = @d_date), 0),

d_surge_stone_sales= coalesce((SELECT sum(coalesce(tons,0))
FROM pms_a_ship_detail
WHERE
pms_a_ship_detail.div = #Operating_Statistics.division AND
pms_a_ship_detail.location = #Operating_Statistics.site AND
pms_a_ship_detail.surge_yn = 'Y' AND
pms_a_ship_detail.tran_date = @d_date), 0) ,

d_secondary_production = coalesce((SELECT sum(coalesce(plant_prod_tons,0))
FROM
pms_a_plant_prod_control
WHERE
pms_a_plant_prod_control.div = #Operating_Statistics.division AND
pms_a_plant_prod_control.location = #Operating_Statistics.site AND
pms_a_plant_prod_control.tran_date = @d_date), 0) ,

d_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'F' AND
pms_a_adj_detail.tran_date = @d_date), 0),

d_secondary_waste = coalesce((SELECT sum(coalesce(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type <> 'P1' AND
pms_a_plant_prod_detail.tran_date = @d_date), 0),

d_reprocess_other = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'O' AND
pms_a_adj_detail.tran_date = @d_date), 0),

d_produced_as_sold = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'P' AND
pms_a_adj_detail.tran_date = @d_date), 0),

d_control_hours_opr= coalesce((SELECT sum(coalesce(hours_oper,0))
FROM
pms_a_oper_hours_control
WHERE
pms_a_oper_hours_control.div = #Operating_Statistics.division AND
pms_a_oper_hours_control.location = #Operating_Statistics.site AND
pms_a_oper_hours_control.type = 'P2' AND
pms_a_oper_hours_control.tran_date = @d_date), 0),

------------------------------------------------------------------------------------------------------------------------YTD FIELDS ----------------------------------------------------------------------------------------------------------------------------------------------

m_truck_loads = coalesce((SELECT sum(coalesce(prim_loads,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

m_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0))
FROM pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location= #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

m_surge_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type= 'S' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
MONTH(pms_a_adj_detail.tran_date) = @month AND
pms_a_adj_detail.tran_date <= @d_date), 0),

m_primary_waste= coalesce((SELECT sum(coalesce(waste_tons,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

m_primary_hours_sch = coalesce((SELECT sum(coalesce(prim_hrs_sched,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

m_primary_hours_opr = coalesce((SELECT sum(coalesce(prim_hrs_oper,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date ), 0),

m_net_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0) - coalesce(waste_tons,0) )
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
MONTH(pms_a_primary_prod_header.tran_date) = @month AND
pms_a_primary_prod_header.tran_date <= @d_date ), 0) +
coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div= #Operating_Statistics.division AND
pms_a_adj_detail.location= #Operating_Statistics.site AND
pms_a_adj_detail.adj_type= 'S' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
MONTH(pms_a_adj_detail.tran_date) = @month AND
pms_a_adj_detail.tran_date <= @d_date ), 0) ,

m_net_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) - COALESCE(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
MONTH(pms_a_plant_prod_detail.tran_date) = @month AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0),

m_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
MONTH(pms_a_plant_prod_detail.tran_date) = @month AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0),

m_surge_stone_sales= coalesce((SELECT sum(coalesce(tons,0))
FROM pms_a_ship_detail
WHERE
pms_a_ship_detail.div = #Operating_Statistics.division AND
pms_a_ship_detail.location = #Operating_Statistics.site AND
pms_a_ship_detail.surge_yn = 'Y' AND
YEAR(pms_a_ship_detail.tran_date) = @Year AND
MONTH(pms_a_ship_detail.tran_date) = @month AND
pms_a_ship_detail.tran_date <= @d_date), 0) ,

m_secondary_production = coalesce((SELECT sum(coalesce(plant_prod_tons,0))
FROM
pms_a_plant_prod_control
WHERE
pms_a_plant_prod_control.div = #Operating_Statistics.division AND
pms_a_plant_prod_control.location = #Operating_Statistics.site AND
YEAR(pms_a_plant_prod_control.tran_date) = @Year AND
MONTH(pms_a_plant_prod_control.tran_date) = @month AND
pms_a_plant_prod_control.tran_date <= @d_date), 0) ,

m_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'F' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
MONTH(pms_a_adj_detail.tran_date) = @month AND
pms_a_adj_detail.tran_date <= @d_date), 0),

m_secondary_waste = coalesce((SELECT sum(coalesce(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type <> 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
MONTH(pms_a_plant_prod_detail.tran_date) = @month AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0),

m_reprocess_other = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'O' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
MONTH(pms_a_adj_detail.tran_date) = @month AND
pms_a_adj_detail.tran_date <= @d_date), 0),

m_produced_as_sold = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'P' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
MONTH(pms_a_adj_detail.tran_date) = @month AND
pms_a_adj_detail.tran_date <= @d_date), 0),

m_control_hours_opr= coalesce((SELECT sum(coalesce(hours_oper,0))
FROM
pms_a_oper_hours_control
WHERE
pms_a_oper_hours_control.div = #Operating_Statistics.division AND
pms_a_oper_hours_control.location = #Operating_Statistics.site AND
pms_a_oper_hours_control.type = 'P2' AND
YEAR(pms_a_oper_hours_control.tran_date) = @Year AND
MONTH(pms_a_oper_hours_control.tran_date) = @month AND
pms_a_oper_hours_control.tran_date <= @d_date), 0),

-- Need to find out how to figure this field
m_capped_production_tons = coalesce((SELECT
CASE @month
WHEN 1 THEN sum(cap_jan)
WHEN 2 THEN sum(cap_feb)
WHEN 3 THEN sum( cap_mar)
WHEN 4 THEN sum( cap_apr)
WHEN 5 THEN sum(cap_may)
WHEN 6 THEN sum(cap_jun)
WHEN 7 THEN sum(cap_jul)
WHEN 8 THEN sum(cap_aug)
WHEN 9 THEN sum(cap_sep)
WHEN 10 THEN sum(cap_oct)
WHEN 11 THEN sum(cap_nov)
WHEN 12 THEN sum(cap_dec)
END
FROM
pms_a_ytd_size_accum
WHERE
pms_a_ytd_size_accum.div = #Operating_Statistics.division AND
pms_a_ytd_size_accum.location = #Operating_Statistics.site AND
pms_a_ytd_size_accum.yr = @Year), 0 ) +
coalesce((SELECT
CASE @month
WHEN 1 THEN sum(cap_adj_jan)
WHEN 2 THEN sum(cap_adj_feb)
WHEN 3 THEN sum(cap_adj_mar)
WHEN 4 THEN sum(cap_adj_apr)
WHEN 5 THEN sum(cap_adj_may)
WHEN 6 THEN sum(cap_adj_jun)
WHEN 7 THEN sum(cap_adj_jul)
WHEN 8 THEN sum(cap_adj_aug)
WHEN 9 THEN sum(cap_adj_sep)
WHEN 10 THEN sum(cap_adj_oct)
WHEN 11 THEN sum(cap_adj_nov)
WHEN 12 THEN sum(cap_adj_dec)
END
FROM
pms_a_ytd_size_accum
WHERE
pms_a_ytd_size_accum.div = #Operating_Statistics.division AND
pms_a_ytd_size_accum.location = #Operating_Statistics.site AND
pms_a_ytd_size_accum.yr = @Year), 0 ) ,

------------------------------------------------------------------------------------------------------------------------YTD FIELDS ----------------------------------------------------------------------------------------------------------------------------------------------

y_truck_loads = coalesce((SELECT sum(coalesce(prim_loads,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

y_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0))
FROM pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

y_surge_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'S' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
pms_a_adj_detail.tran_date <= @d_date), 0),

y_primary_waste= coalesce((SELECT sum(coalesce(waste_tons,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0) ,

y_primary_hours_sch = coalesce((SELECT sum(coalesce(prim_hrs_sched,0))
FROM
pms_a_primary_prod_header

WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

y_primary_hours_opr = coalesce((SELECT sum(coalesce(prim_hrs_oper,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location = #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0),

y_net_primary_production = coalesce((SELECT sum(coalesce(prim_tons,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location= #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0) -
coalesce((SELECT sum(coalesce(waste_tons,0))
FROM
pms_a_primary_prod_header
WHERE
pms_a_primary_prod_header.div = #Operating_Statistics.division AND
pms_a_primary_prod_header.location= #Operating_Statistics.site AND
YEAR(pms_a_primary_prod_header.tran_date) = @Year AND
pms_a_primary_prod_header.tran_date <= @d_date), 0) +
coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location= #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'S' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
pms_a_adj_detail.tran_date <= @d_date), 0) ,

y_net_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) - COALESCE(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0) ,

y_primary_finished_products= coalesce((SELECT sum(coalesce(prod_tons, 0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type = 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0) ,

y_surge_stone_sales= coalesce((SELECT sum(coalesce(tons,0))
FROM pms_a_ship_detail
WHERE
pms_a_ship_detail.div = #Operating_Statistics.division AND
pms_a_ship_detail.location = #Operating_Statistics.site AND
pms_a_ship_detail.surge_yn = 'Y' AND
YEAR(pms_a_ship_detail.tran_date) = @Year AND
pms_a_ship_detail.tran_date <= @d_date), 0) ,

y_secondary_production = coalesce((SELECT sum(coalesce(prod_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type <> 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0),

y_fly_over_adj = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'F' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
pms_a_adj_detail.tran_date <= @d_date), 0),

y_reprocess_other= coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'O' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
pms_a_adj_detail.tran_date <= @d_date), 0),

y_secondary_waste = coalesce((SELECT sum(coalesce(waste_tons,0) )
FROM
pms_a_plant_prod_detail,
pms_r_plants
WHERE
pms_a_plant_prod_detail.div = #Operating_Statistics.division AND
pms_a_plant_prod_detail.location = #Operating_Statistics.site AND
pms_r_plants.div = #Operating_Statistics.division AND
pms_r_plants.location = #Operating_Statistics.site AND
pms_r_plants.id = pms_a_plant_prod_detail.plant_id AND
pms_r_plants.type <> 'P1' AND
YEAR(pms_a_plant_prod_detail.tran_date) = @Year AND
pms_a_plant_prod_detail.tran_date <= @d_date), 0),

y_produced_as_sold = coalesce((SELECT sum(coalesce(amount,0))
FROM
pms_a_adj_detail
WHERE
pms_a_adj_detail.div = #Operating_Statistics.division AND
pms_a_adj_detail.location = #Operating_Statistics.site AND
pms_a_adj_detail.adj_type = 'P' AND
YEAR(pms_a_adj_detail.tran_date) = @Year AND
pms_a_adj_detail.tran_date <= @d_date), 0),

y_control_hours_opr= coalesce((SELECT sum(coalesce(hours_oper,0))
FROM
pms_a_oper_hours_control
WHERE
pms_a_oper_hours_control.div = #Operating_Statistics.division AND
pms_a_oper_hours_control.location = #Operating_Statistics.site AND
pms_a_oper_hours_control.type = 'P2' AND
YEAR(pms_a_oper_hours_control.tran_date) = @Year AND
pms_a_oper_hours_control.tran_date <= @d_date), 0),

-- Need to find out how to figure this field
y_capped_production_tons = coalesce((SELECT
CASE @month
WHEN 1 THEN sum(cap_jan)
WHEN 2 THEN sum(cap_feb+cap_jan)
WHEN 3 THEN sum( cap_mar+cap_feb+cap_jan)
WHEN 4 THEN sum( cap_apr+ cap_mar+cap_feb+cap_jan)
WHEN 5 THEN sum(cap_may+ cap_apr+ cap_mar+cap_feb+cap_jan)
WHEN 6 THEN sum(cap_jun+cap_may+ cap_apr+ cap_mar+cap_feb+cap_jan)
WHEN 7 THEN sum(cap_jul+cap_jun+cap_may+cap_apr+cap_mar+cap_feb+cap_jan)
WHEN 8 THEN sum(cap_aug+cap_jul+cap_jun+cap_may+cap_apr+cap_mar+cap_feb+cap_jan)
WHEN 9 THEN sum(cap_sep+cap_aug+cap_jul+cap_jun+cap_may+cap_apr+cap_mar+cap_feb+cap_jan)
WHEN 10 THEN sum(cap_oct+cap_sep+cap_aug+cap_jul+cap_jun+cap_may+cap_apr+ cap_mar+cap_feb+cap_jan)
WHEN 11 THEN sum(cap_nov+cap_oct+cap_sep+cap_aug+cap_jul+cap_jun+cap_may+ cap_apr+ cap_mar+cap_feb+cap_jan)
WHEN 12 THEN sum(cap_dec+cap_nov+cap_oct+cap_sep+cap_aug+cap_jul+cap_jun+cap_may+ cap_apr+ cap_mar+cap_feb+cap_jan)
END
FROM
pms_a_ytd_size_accum
WHERE
pms_a_ytd_size_accum.div = #Operating_Statistics.division AND
pms_a_ytd_size_accum.location = #Operating_Statistics.site AND
pms_a_ytd_size_accum.yr = @Year), 0 ) +
coalesce((SELECT
CASE @month
WHEN 1 THEN sum(cap_adj_jan)
WHEN 2 THEN sum(cap_adj_feb+cap_adj_jan)
WHEN 3 THEN sum(cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 4 THEN sum(cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 5 THEN sum(cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 6 THEN sum(cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 7 THEN sum(cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 8 THEN sum(cap_adj_aug+cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 9 THEN sum(cap_adj_sep+cap_adj_aug+cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 10 THEN sum(cap_adj_oct+cap_adj_sep+cap_adj_aug+cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 11 THEN sum(cap_adj_nov+cap_adj_oct+cap_adj_sep+cap_adj_aug+cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
WHEN 12 THEN sum(cap_adj_dec+cap_adj_nov+cap_adj_oct+cap_adj_sep+cap_adj_aug+cap_adj_jul+cap_adj_jun+cap_adj_may+cap_adj_apr+cap_adj_mar+cap_adj_feb+cap_adj_jan)
END
FROM
pms_a_ytd_size_accum
WHERE
pms_a_ytd_size_accum.div = #Operating_Statistics.division AND
pms_a_ytd_size_accum.location = #Operating_Statistics.site AND
pms_a_ytd_size_accum.yr = @Year), 0 ) ,
output_date = @d_date

SELECT
#Operating_Statistics.division,
vs_r_div.Div_Nm,
#Operating_Statistics.site,
vs_r_dept.dept_nm,
#Operating_Statistics.output_date,
-- DAILY FIELDS
-- #Operating_Statistics.d_truck_loads,
-- #Operating_Statistics.d_primary_production,
-- #Operating_Statistics.d_surge_fly_over_adj,
-- #Operating_Statistics.d_primary_waste,
-- #Operating_Statistics.d_primary_hours_sch,
-- #Operating_Statistics.d_primary_hours_opr,
-- #Operating_Statistics.d_net_primary_production,
-- #Operating_Statistics.d_primary_finished_products,
-- #Operating_Statistics.d_surge_stone_sales,
-- #Operating_Statistics.d_secondary_production,
-- #Operating_Statistics.d_fly_over_adj,
-- #Operating_Statistics.d_secondary_waste,
-- #Operating_Statistics.d_reprocess_other,
-- #Operating_Statistics.d_produced_as_sold,
-- #Operating_Statistics.d_control_hours_opr,

#Operating_Statistics.d_secondary_production +
#Operating_Statistics.d_fly_over_adj -
#Operating_Statistics.d_secondary_waste +
#Operating_Statistics.d_reprocess_other +
#Operating_Statistics.d_net_primary_finished_products +
#Operating_Statistics.d_produced_as_sold AS d_net_accounting_production,

-- MONTHLY FIELDS
-- #Operating_Statistics.m_truck_loads,
-- #Operating_Statistics.m_primary_production,
-- #Operating_Statistics.m_surge_fly_over_adj,
-- #Operating_Statistics.m_primary_waste,
-- #Operating_Statistics.m_primary_hours_sch,
-- #Operating_Statistics.m_primary_hours_opr,
-- #Operating_Statistics.m_net_primary_production,
-- #Operating_Statistics.m_primary_finished_products,
-- #Operating_Statistics.m_surge_stone_sales,
-- #Operating_Statistics.m_secondary_production,
-- #Operating_Statistics.m_fly_over_adj,
-- #Operating_Statistics.m_secondary_waste,
-- #Operating_Statistics.m_reprocess_other,
-- #Operating_Statistics.m_produced_as_sold,
-- #Operating_Statistics.m_control_hours_opr,
-- #Operating_Statistics.m_capped_production_tons,

#Operating_Statistics.m_secondary_production +
#Operating_Statistics.m_fly_over_adj -
#Operating_Statistics.m_secondary_waste +
#Operating_Statistics.m_reprocess_other +
#Operating_Statistics.m_net_primary_finished_products +
#Operating_Statistics.m_produced_as_sold +
#Operating_Statistics.m_capped_production_tons AS m_net_accounting_production,

-- YTD FIELDS
-- #Operating_Statistics.y_truck_loads,
-- #Operating_Statistics.y_primary_production,
-- #Operating_Statistics.y_surge_fly_over_adj,
-- #Operating_Statistics.y_primary_waste,
-- #Operating_Statistics.y_primary_hours_sch,
-- #Operating_Statistics.y_primary_hours_opr,
-- #Operating_Statistics.y_net_primary_production,
-- #Operating_Statistics.y_primary_finished_products,
-- #Operating_Statistics.y_surge_stone_sales,
-- #Operating_Statistics.y_secondary_production,
-- #Operating_Statistics.y_fly_over_adj,
-- #Operating_Statistics.y_secondary_waste,
-- #Operating_Statistics.y_reprocess_other,
-- #Operating_Statistics.y_produced_as_sold,
-- #Operating_Statistics.y_control_hours_opr,
-- #Operating_Statistics.y_capped_production_tons,
-- #Operating_Statistics.d_net_primary_finished_products,
-- #Operating_Statistics.w_net_primary_finished_products,
-- #Operating_Statistics.y_net_primary_finished_products,

#Operating_Statistics.y_secondary_production +
#Operating_Statistics.y_fly_over_adj -
#Operating_Statistics.y_secondary_waste +
#Operating_Statistics.y_reprocess_other +
#Operating_Statistics.y_net_primary_finished_products +
#Operating_Statistics.y_produced_as_sold +
#Operating_Statistics.y_capped_production_tons AS y_net_accounting_production

-- #Operating_Statistics.min_surge,
-- #Operating_Statistics.max_surge
FROM
#Operating_Statistics,
vs_r_div,
vs_r_dept
WHERE
#Operating_Statistics.Division = vs_r_div.Div AND
#Operating_Statistics.Division = vs_r_dept.Div AND
#Operating_Statistics.Site = vs_r_dept.dept
ORDER BY
vs_r_div.Div_Nm,
vs_r_dept.dept ASC

DROP TABLE #Operating_Statistics
END

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-25 : 12:29:53

Does it work in Query analyzer?

varneym
Starting Member

3 Posts

Posted - 2008-06-25 : 13:40:07

Yes, it does. I have changed it from a temporary table to a regular table by removing the #.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 03:07:39

quote:
Originally posted by varneym

Yes, it does. I have changed it from a temporary table to a regular table by removing the #.

Can you put this in a procedure and try calling it from DTS using Execute SQL task?

varneym
Starting Member

3 Posts

Posted - 2008-06-30 : 11:01:28

Do I just save the query as a procedure to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 11:08:42

quote:
Originally posted by varneym

Do I just save the query as a procedure to do this?

i was asking you to have a try. Write a procedure with your current code, create it in the db where you want code to be executed. Then add an execute sql task in your DTS with connection pointing to that db and give Exec SP paramvalues... as the statement.

singh_nirajan
Starting Member

2 Posts

Posted - 2008-07-07 : 09:33:00

Try with Stored procedure using

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

after AS and using stored procedures in DTS

Nirajan Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources