trouble with temp tables and UNION keyword

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

Transact-SQL (2000)

trouble with temp tables and UNION keyword

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-18 : 11:45:06

I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate data that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the work in process (WIP) cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is:
ToDate (cost of materials and labor assigned to job)
- ToInv (cost of materials returned to inventory)
- ToSales (cost of materials sold).

I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error:
------------------------------------------
Incorrect syntax near the keyword 'UNION'.
------------------------------------------
The problem is with the UNIONs going into #myTotal.

I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!

Below is a simplified version of my query:

--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)

--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

UNION -- This one works ok.

--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum

--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)

SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum

UNION -- Problem**********************

SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum

UNION -- Problem**********************

SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum

--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum

--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 12:05:00

Change like this

SELECT tmp1.jobnum,
      (tmp1.Cost +tmp2.Cost) -(tmp3.ToInv+tmp4.ToInv) AS
FROM
(SELECT pt.jobnum, 
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum) tmp1
INNER JOIN 
(SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1 
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum)tmp2
ON tmp2.JobNum=tmp1.JobNum
INNER JOIN
(SELECT pt.jobnum, 
SUM(pt.extcost) AS ToInv 
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum)tmp3
ON tmp3.jobnum=tmp2.jobnum
INNER JOIN 
(SELECT pt.jobnum, 
SUM(pt.extcost) AS ToInv 
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
)tmp4
ON tmp4.jobnum=tmp3.jobnum

three of query batches looks same...cant understand why you need them seperately.

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-18 : 12:21:46

Thanks, visakh16, for your response.

My reason for having the separate query batches is that I need to be able to filter on transaction types that get added to the cost, transaction types that need to be subtracted as $ back to inventory, and transaction types that need to be subtracted as $ to sales.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 12:25:27

can you give your full queries. then i may be able to further simplify this.

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-18 : 12:55:38

Here is the full query. I removed the ORDER BY in the SELECTs around the UNIONs and am no longer getting the error. It looks like the data is correct. However, I would welcome and improvements you can offer. Thanks again.

--Totals.sql
declare @CutoffDate datetime
set @CutoffDate = DATEADD(day,1,'2007-9-30')

--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToDate (JobNum, Cost)

--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost)
FROM parttran pt
JOIN jobhead jh ON pt.Company=jh.Company AND pt.jobnum=jh.jobnum
WHERE trantype IN
(

'PUR-MTL', --Purchase recipt to job material
'PUR-SUB', --Purchase recipt to subcontract
'STK-MTL', --Stock to job material
'STK-ASM', --Stock to job assembly
'ADJ-PUR', --Adjustment to purchase cost variance
'ADJ-MTL', --Adjustment to job cost material
'ADJ-SUB', --Adjustment to job cost subcontract
'SVG-STK', --Salvage material receipt to stock
'DMR-MTL', --DMR to job material
'DMR-SUB', --DMR to subcontract
'MTL-DMR', --Job material to DMR (replaced by MTL-INS)
'SUB-DMR', --Subcontract to DMR
'INS-SUB', --Inspection to subcontract
'INS-MTL', --Inspection to job material
'MTL-INS' --Job material to inspection
)
AND jh.JobReleased = 1 AND jh.JobClosed<>1
AND pt.JobSeqType IN('M', 'S')
AND pt.TranDate < @CutoffDate
GROUP BY pt.jobnum

UNION

--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs)
FROM LaborDtl l
JOIN JobHead jh ON l.Company = jh.Company AND l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1 -- Show released jobs
AND jh.JobClosed<>1 -- Show open jobs
AND l.PayrollDate < @CutoffDate
GROUP BY jh.JobNum

--SELECT JobNum, SUM(Cost) AS ToDate FROM #ToDate GROUP BY JobNum ORDER BY JobNum
--DROP TABLE #ToDate

--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToInv (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost)
FROM parttran pt
JOIN jobhead jh ON pt.Company=jh.Company AND pt.jobnum=jh.jobnum
WHERE (trantype IN ('mfg-stk'))
AND jh.JobReleased = 1 AND jh.JobClosed<>1
AND pt.JobSeqType = 'M'-- Show material transactions
AND pt.TranDate < @CutoffDate
GROUP BY pt.jobnum

--SELECT JobNum, SUM(Cost) AS ToInv FROM #ToInv GROUP BY JobNum ORDER BY JobNum
--DROP TABLE #ToInv

--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost)
FROM parttran pt
JOIN jobhead jh ON pt.Company=jh.Company AND pt.jobnum=jh.jobnum
WHERE (trantype IN ('mfg-cus', 'stk-cus'))
AND jh.JobReleased = 1 AND jh.JobClosed<>1
AND pt.JobSeqType = 'M'-- Show material transactions
AND pt.TranDate < @CutoffDate
GROUP BY pt.jobnum

--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2))

INSERT INTO #myTotal (JobNum, Cost)
SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum

UNION

SELECT i.JobNum, SUM(-1*i.Cost) FROM #ToInv i GROUP BY i.JobNum

UNION

SELECT s.JobNum, SUM(-1*s.Cost) FROM #ToSales s GROUP BY s.JobNum

SELECT JobNum, SUM(Cost) AS [Job Totals] FROM #myTotal GROUP BY JobNum ORDER BY JobNum

DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 13:24:41

[code]CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToDate (JobNum, Cost)

SELECT pt.jobnum,
SUM(CASE
WHEN trantype IN
(

'PUR-MTL', --Purchase recipt to job material
'PUR-SUB', --Purchase recipt to subcontract
'STK-MTL', --Stock to job material
'STK-ASM', --Stock to job assembly
'ADJ-PUR', --Adjustment to purchase cost variance
'ADJ-MTL', --Adjustment to job cost material
'ADJ-SUB', --Adjustment to job cost subcontract
'SVG-STK', --Salvage material receipt to stock
'DMR-MTL', --DMR to job material
'DMR-SUB', --DMR to subcontract
'MTL-DMR', --Job material to DMR (replaced by MTL-INS)
'SUB-DMR', --Subcontract to DMR
'INS-SUB', --Inspection to subcontract
'INS-MTL', --Inspection to job material
'MTL-INS' --Job material to inspection
) AND pt.JobSeqType IN('M', 'S')
THEN pt.extcost
WHEN trantype IN ('mfg-stk','mfg-cus', 'stk-cus') AND pt.JobSeqType = 'M'
THEN (-1) * pt.extcost
END)
FROM parttran pt
JOIN jobhead jh ON pt.Company=jh.Company AND pt.jobnum=jh.jobnum
WHERE jh.JobReleased = 1 AND jh.JobClosed<>1
AND pt.TranDate < @CutoffDate
GROUP BY pt.jobnum

UNION

SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs)
FROM LaborDtl l
JOIN JobHead jh ON l.Company = jh.Company AND l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1 -- Show released jobs
AND jh.JobClosed<>1 -- Show open jobs
AND l.PayrollDate < @CutoffDate
GROUP BY jh.JobNum

SELECT JobNum, SUM(Cost) AS [Job Totals] FROM #ToDate GROUP BY JobNum ORDER BY JobNum[/code]

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-19 : 08:12:09

That looks great! It's about 4 x as fast as the original query with the same results.
Thank you, Visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources