SELECT
 ISNULL(dt1.PartID,dt2.PartID) as PartID,
 ISNULL(dt1.OtherID,dt2.OtherID) as OtherID,
 ISNULL(dt1.[Hasil Produksi],0) as [Hasil Produksi],
 ISNULL(dt2.[Hasil Repair],0) as [Hasil Repair]
FROM
(
 SELECT 
 a.PartID, 
 b.OtherID, 
 SUM(a.Qty) AS [Hasil Produksi], 
 '0' AS [Hasil Repair]
 FROM Trans_BHPDt201001 AS a 
 INNER JOIN Ms_Part201001 AS b 
   ON a.PartID = b.PartID 
 INNER JOIN Trans_BHPHd201001 AS c 
   ON a.NoBukti = c.NoBukti
 WHERE (a.LocationID LIKE 'WH 2%') AND 
       (c.Tgl BETWEEN '20100101' AND '20100131')
 GROUP BY a.PartID, b.OtherID
) as dt1

FULL OUTER JOIN

(
 SELECT 
 d.PartID, 
 f.OtherID, 
 '0' AS [Hasil Produksi], 
 SUM(d.Qty) AS [Hasil Repair]
 FROM Trans_GdgMoveDt201001 AS d 
 INNER JOIN Ms_Part201001 AS f 
   ON d.PartID = f.PartID 
 INNER JOIN Trans_GdgMoveHd201001 AS g 
   ON d.NoBukti = g.NoBukti
 WHERE (g.LocationID_From LIKE 'QC N') AND 
       (g.LocationID_To LIKE 'WH 2%') AND 
       (g.Tgl BETWEEN '20100101' AND '20100131')
 GROUP BY d.PartID, f.OtherID
) as dt2

ON dt1.PartID = dt2.PartID and
   dt1.OtherID = dt2.OtherID

quote:

---

Dear web,

from my first case, it already solved it, but the problem is, from the query only display the 'RI%' section that only got from the first query.

after I try the query, the other 'RI' that not in the list of 'MS_Part' it didn't show up.

---

select 
 m.PartID,
 m.OtherID,
 isnull(Qty_Produksi.Qty,0) as Qty_Produksi,
 isnull(Qty_Repair.Qty,0) as Qty_Repair
from [Master Part] m
left join Qty_Produksi
  on Qty_Produksi.PartID = m.PartID
left join Qty_Repair
  on Qty_Repair.PartID = m.PartID

quote:

---

Originally posted by cariduit

webfred, if I want to insert another two select again, how about the syntax?

is it like this ?

SELECT
{

}
FROM
{
SELECT
{

}
}
FULL OUTER JOIN
{
SELECT
{

}
}
FULL OUTER JOIN
{
SELECT
{

}
}
FULL OUTER JOIN
{
SELECT
{

}
}

or, something like the else? please help me..

---

quote:

---

Originally posted by cariduit

no webfred,

actually like this

I have a table called 'MS_Part' where the data like this :

001 | RI301
002 | RI302
003 | RI303
004 | RI304
005 | RI305

the first table called 'Produksi' have the data like this :
001 | 10
002 | 5
001 | 2

the second table called 'Repair' have the data like this :
001 | 5
003 | 6

the third table called 'Retur' have the data like this :
003 | 2
004 | 1

and the last table called 'Sales' have the data like this :
001 | 5
003 | 7

the result should be like this :
001 | RI301 | 12 | 5 | 0 | 5
002 | RI302 | 5 | 0 | 0 | 0
003 | RI303 | 0 | 6 | 2 | 7
004 | RI304 | 0 | 0 | 1 | 0
005 | RI305 | 0 | 0 | 0 | 0

need your help so much.

---

SELECT m.PartID,m.OtherID,
SUM(CASE WHEN Cat=1 THEN TotalQty ELSE 0 END) AS TotalQty1,
SUM(CASE WHEN Cat=2 THEN TotalQty ELSE 0 END) AS TotalQty2,
SUM(CASE WHEN Cat=3 THEN TotalQty ELSE 0 END) AS TotalQty3,
SUM(CASE WHEN Cat=4 THEN TotalQty ELSE 0 END) AS TotalQty4
FROM MS_Part m
LEFT JOIN 
(
SELECT PartID,SUM(Qty) AS TotalQty,1 AS Cat
FROM Produksi
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),2
FROM Repair
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),3
FROM Retur
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),4
FROM Sales
GROUP BY PartID
)t
ON t.PartID=m.PartID
GROUP BY m.PartID,m.OtherID

quote:

---

Originally posted by visakh16

Can there be multiple records per PartID in tables like Qty_Produksi,Qty_Repair etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by cariduit

quote:

---

Originally posted by visakh16

Can there be multiple records per PartID in tables like Qty_Produksi,Qty_Repair etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

yes visak.. there could be multiple recoreds in other tables

---

select  * into #ms_part
from
(select '001' as partid,'RI301' as Otherid union all
select '002','RI302' union all
select '003','RI303' union all
select '004','RI304' union all
select '005','RI305' 
 )t
 
 select  * into #Produksi
from
(select '001' as partid,10 AS Qty union all
select '002',5 union all
select '001', 2
 )t
 
 select  * into #Repair
from
(select '001' as partid,5 AS Qty union all
select '003',6 
 )t
 
 select  * into #Retur
from
(select '003' as partid,2 AS Qty union all
select '004',1 
 )t
 
 select  * into #Sales
from
(select '001' as partid,5 AS Qty union all
select '003',7 
 )t
 
 
 SELECT m.PartID,m.OtherID,
SUM(CASE WHEN Cat=1 THEN TotalQty ELSE 0 END) AS TotalQty1,
SUM(CASE WHEN Cat=2 THEN TotalQty ELSE 0 END) AS TotalQty2,
SUM(CASE WHEN Cat=3 THEN TotalQty ELSE 0 END) AS TotalQty3,
SUM(CASE WHEN Cat=4 THEN TotalQty ELSE 0 END) AS TotalQty4
FROM #MS_Part m
LEFT JOIN 
(
SELECT PartID,SUM(Qty) AS TotalQty,1 AS Cat
FROM #Produksi
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),2
FROM #Repair
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),3
FROM #Retur
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),4
FROM #Sales
GROUP BY PartID
)t
ON t.PartID=m.PartID
GROUP BY m.PartID,m.OtherID

drop table #MS_Part

drop table #Produksi
drop table #Repair
drop table #Retur
drop table #Sales



output
--------------------------------------------
001	RI301	12	5	0	5
002	RI302	5	0	0	0
003	RI303	0	6	2	7
004	RI304	0	0	1	0
005	RI305	0	0	0	0

quote:

---

Originally posted by cariduit

dear visak,

in your syntax query, the items in MS_part must be list all?

how about if I have thousand of items in MS_part? should I list of all?

---

SELECT m.PartID,m.OtherID,
SUM(CASE WHEN Cat=1 THEN TotalQty ELSE 0 END) AS TotalQty1,
SUM(CASE WHEN Cat=2 THEN TotalQty ELSE 0 END) AS TotalQty2,
SUM(CASE WHEN Cat=3 THEN TotalQty ELSE 0 END) AS TotalQty3,
SUM(CASE WHEN Cat=4 THEN TotalQty ELSE 0 END) AS TotalQty4
FROM #MS_Part m
LEFT JOIN 
(
SELECT PartID,SUM(Qty) AS TotalQty,1 AS Cat
FROM #Produksi
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),2
FROM #Repair
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),3
FROM #Retur
GROUP BY PartID

UNION ALL

SELECT PartID,SUM(Qty),4
FROM #Sales
GROUP BY PartID
)t
ON t.PartID=m.PartID
GROUP BY m.PartID,m.OtherID