MS SQL Server MSEQ - TABLE Joins Problem

Author

TABLE Joins Problem

Jan S via SQLMonster.com

2005-08-11, 3:25 am

I have two sets of Data. For example Sales And Purchasing.

SALES(ISAL)
PURCHASING(IPUR)
-------------------------------
------------------------------------
ItemGroup
ItemGroup
ItemCode
ItemCode
DocDate
DocDate
Quantity
Quantity
Total
Total

The data mentioned above are all different with varying no. of rows in each

I need to do a join of the table just to show a simple summary by Item Group

For Example:

ItemGroup Total Sales Sold Qty Total Purchases
Purchased Qty
-------------- ------------------ ----------- -------
--------------- ---------------------
ACN 23432.90 234 1243.90
89
Etc.....

I am able to use the SUM Function for each table separately but when i join
the two, the values get all messed up. There is also a Master Table IGRP
which stores all the groups. There is also user variables
used to allow the user to specify the range of dates(but lets ignore that for
now).

What i tried:
SELECT T2.ItemGroup, SUM(T0.Total) as 'Total Purchases', SUM(T0.Quantity) as
'Purchased Qty', SUM(T1.Total) as 'Total Sales', SUM(T1.Quantity) as 'Sold
Qty' FROM IPUR T0 FULL OUTER JOIN ISAL T1 ON V1.ItemCode = V0.ItemCode INNER
JOIN IGRP ON T2.ItemGroup = T0.ItemGroup OR T2.ItemGroup = T1.ItemGroup GROUP
BY T3.ItemGroup

The resultset returned is correct for some entries but 2-5x higher for most.

Please help...Is there a way or do i just have to seperate both of them?

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...r-mseq/200508/1

Jan S via SQLMonster.com

2005-08-11, 3:25 am

Sorry abt the crappy illustrations...Here it is again.
SALES(ISAL) PURCHASING(IPUR)
--------------------- -----------------------------
ItemGroup ItemGroup
ItemCode ItemCode
DocDate DocDate
Quantity Quantity
Total Total

What i need:
ItemGroup Total Sales Sold Qty Total Purchases Purchased
Qty
----------------- ---------------- ----------- ------------------
---- ---------------------
ACN 23432.90 234 1243.90
89
Etc.....

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...r-mseq/200508/1

Hugo Kornelis

2005-08-11, 8:24 pm

On Thu, 11 Aug 2005 02:47:08 GMT, Jan S via webservertalk.com wrote:

>Sorry abt the crappy illustrations...Here it is again.
>SALES(ISAL) PURCHASING(IPUR)
>--------------------- -----------------------------
>ItemGroup ItemGroup
>ItemCode ItemCode
>DocDate DocDate
>Quantity Quantity
>Total Total
>
>
>What i need:
> ItemGroup Total Sales Sold Qty Total Purchases Purchased
>Qty
>----------------- ---------------- ----------- ------------------
>---- ---------------------
> ACN 23432.90 234 1243.90
>89
>Etc.....

Hi Jan,

The best way to post your table structure and sample data as to use
CREATE TABLE and INSERT statements - that also has the added advantage
of providing easy to copy-and-paste test data! See www.aspfaq.com/5006
for more info on the best way to ask for help in these groups.

Anyway, here are two untested queries that will probably both return the
results you need. Use the one you like best, or use them as a basis for
your own version.

SELECT s.ItemGroup,
SUM(s.Total) AS TotalSales,
SUM(s.Quantity) AS SoldQty,
(SELECT SUM(p.Total)
FROM Purchases AS p
WHERE p.ItemGroup = s.ItemGroup) AS TotalPurchases,
(SELECT SUM(p.Quantity)
FROM Purchases AS p
WHERE p.ItemGroup = s.ItemGroup) AS PurchasedQty
FROM Sales AS s
GROUP BY s.ItemGroup

or

SELECT s.ItemGroup,
s.TotalSales, s.SoldQty,
p.TotalPurchases, p,PurchasedQty
FROM (SELECT ItemGroup, SUM(Total), SUM(Quantity)
FROM Sales
GROUP BY ItemGroup) AS s(ItemGroup, TotalSales, SoldQty)
INNER JOIN (SELECT ItemGroup, SUM(Total), SUM(Quantity)
FROM Purchases
GROUP BY ItemGroup) AS p(ItemGroup, TotalPurchases,
PurchasedQty)
ON p.ItemGroup = s.ItemGroup

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links