| Author |
 Topic  |
|
|
netagra
Starting Member
Greece
3 Posts |
 Posted - 12/07/2010 : 07:25:12
|
Hello to all,
it's nice to find you.
I have two tables one with production data the other is scrap data. The tables look pretty much identical, though i cannot make my query work.
Production table is like this
PrDate, PrUser, PrMachine, PrDept, PrItemCode, PrQuantity
Scrap table is like this
ScDate, ScUser, ScMachine, ScDept, ScItemCode, ScQuantity
What i need is to produce one table with a specific date that has both production and scrap quantities per user, item code and machine
What i have done is this
SELECT PrUser, PrMachine, PrItemCode, PrDept, SUM(PrQuantity), Sum(ScQuantity)
FROM Production LEFT JOIN Scrap ON PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
WHERE (PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00') AND PrDept = '611.4' AND Q9USER = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
ORDER BY PrUser, PrMachine
But it doesn't work. It produces crazy amounts on sums! If i run two different queries each for the specific table i get my answers. The problem is i cannot join them in one table result.
What's wrong?
Thanks for your time! |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/07/2010 : 07:43:23
|
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity, ScQuantity
from
(
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity = SUM(PrQuantity)
from Production
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND Q9USER = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
) pr
left join
(
select ScUser, ScMachine, ScItemCode, ScDept, ScQuantity = SUM(ScQuantity)
from Scrap
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND Q9USER = '01225E'
GROUP BY ScUser, ScMachine, ScItemCode, ScDept
) sc
on PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
ORDER BY PrUser, PrMachine
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
netagra
Starting Member
Greece
3 Posts |
Posted - 12/07/2010 : 08:11:36
|
Thanks for your reply.
It doesn't like the
PrQuantity = SUM(PrQuantity)
ScQuantity = SUM(ScQuantity)
or something else?
This is what i get
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '='.
And sorry Q9User is PrUser ! |
Edited by - netagra on 12/07/2010 08:33:44 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/07/2010 : 08:31:54
|
Looks ok - is this sql server?
try
select PrUser, PrMachine, PrItemCode, PrDept, SUM(PrQuantity) as PrQuantity
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
netagra
Starting Member
Greece
3 Posts |
Posted - 12/07/2010 : 09:40:07
|
Thanks again!
It worked perfectly.
I made some modifications and it looks like this
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity, ScQuantity
from
(
select PrUser, PrMachine, PrItemCode, PrDept, PrQuantity = SUM(PrQuantity)
from Production
where PrDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND PrDept = '611.4'
AND PrUser = '01225E'
GROUP BY PrUser, PrMachine, PrItemCode, PrDept
) pr
left join
(
select ScUser, ScMachine, ScItemCode, ScDept, ScQuantity = SUM(ScQuantity)
from Scrap
where ScDate Between '2010-01-11 06:00' And '2010-10-11 06:00'
AND ScDept = '611.4'
AND ScUser = '01225E'
GROUP BY ScUser, ScMachine, ScItemCode, ScDept
) sc
on PrUser = ScUser AND PrMachine = ScMachine AND PrItemCode = ScItemCode AND PrDept = ScDept
ORDER BY PrUser, PrMachine, PrItem, PrStep
It combines these two queries in one and makes the results i need.
What is this technique called in order to work more with ?
Thank you so much. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/07/2010 : 09:43:50
|
pr and sc are called derived tables.
You could also do the same thing with common table expressions which is sometomes more useful.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
| |
Topic |
|
|
|
Inner Join two tables with two different SUMs
