JOIN 4 tables, how to simplify this?

Question

1.00/5 (1 vote)

See more:

Hi everyone, i simplified the table as simple as possible to easy understanding, and i need to calculate the all stock balance. 4 tables as below:

HTML

product
   prtID   prtQty
     1        1
     2        4
     3        4

stockInOut (I=In, O=Out)
  IOID  prtType  prtID    IOQty      IODate
   1       I      1        6       2013-08-07
   2       O      2        1       2013-08-08
   3       O      1        1       2013-08-08

sell
  sellID   prtID    sellQty      sellDate
    1        1         2        2013-08-05
    2        2         2        2013-08-10

reject
  rejID    prtID    rejQty       rejDate
    1        1        1        2013-08-09
    2        2        1        2013-08-10

Here is the calculation:

HTML

Balance = prtQty + IOQty(prtType=I) - IOQty(prtType=O) - sellQty - rejID

Example balance for prtID = 1

HTML

Balance = 1 + 6 - 1 - 2 - 1

I only done for 2 table and it's already very complicated, i wish there is a simple way of doing this :)
this is what i have done so far:

HTML

SELECT prtID, SUM(IOQty)+(prtQty) AS balance
FROM product P
LEFT JOIN stockInOut IO ON P.prtID= IO.prtID
WHERE prtType = 'I' AND DATE(IODate) BETWEEN '2013-08-01' AND '2013-08-31'
GROUP BY prtID
UNION ALL 
SELECT prtID, prtQty FROM product 
WHERE prtID NOT IN 
(SELECT prtID FROM stockInOut WHERE prtType = 'I' AND DATE(IODate) BETWEEN '2013-08-01' AND '2013-08-31' ORDER BY prt_ID

Expected Output:

HTML

prtID   balance
  1       3
  2       0
  3       4

* first field(ID) is the autoNumber.
* Date Format: yyyy-MM-dd

any advice?

Posted 11-Aug-13 3:40am

melvintcs

Updated 11-Aug-13 4:20am

v4

Add a Solution

Comments

Maciej Los 11-Aug-13 10:14am

Is it possible that rejId exists twice in reject table?
Please, post expected output.

melvintcs 11-Aug-13 10:20am

hi there, already update the reject table and expected output is added

4 solutions

Solution 2

I would claim that you could make it a lot easier with the correct table structure.

product
   prtID   
     1
     2
     3

 
stockInOut 
  IOID  prtType  prtID    IOQty   IODate
  1     Initial  1         1      2013-01-01
  2     Initial  2         4      2013-01-01
  3     Initial  3         4      2013-01-01
  4     Sell     1        -2      2013-08-05
  5     In       1         6      2013-08-07
  6     Out      2        -1      2013-08-08
  7     Out      1        -1      2013-08-08
  8     Reject   1        -1      2013-08-09
  9     Sell     2        -2      2013-08-10
 10     Reject   2        -1      2013-08-10

Now you get the balance by this query:

Select  PrtID,Sum(IOQty) Balance
From StockInOut
Group by PrtID
Order by PrtID

Posted 11-Aug-13 8:39am

Jörgen Andersson

Updated 12-Aug-13 8:21am

v3

Comments

Maciej Los 11-Aug-13 15:10pm

Good advice and hawk eye ;)
+5!

Jörgen Andersson 11-Aug-13 15:24pm

Thanks

melvintcs 12-Aug-13 10:45am

this is a great idea, but i believe that ur query is only return prtID 1 and 2. 3 wont show in the result..

Jörgen Andersson 12-Aug-13 11:00am

Updated typo.

melvintcs 12-Aug-13 11:07am

ic... actually why im putting a qty at product table this is to store the quantity balance after do the house keeping (2 years once), so all the transaction between this 2 years will be erased. hence, we still need to join back the product table to get the initial quantity. can you update ur answer again? and i will mark it as answer :) tq

Jörgen Andersson 12-Aug-13 14:23pm

Nah, you just need another type for the initial values. Updated solution to reflect this.

Maciej Los 12-Aug-13 17:18pm

If i could i'll give you another 5 ;)

melvintcs 12-Aug-13 20:41pm

im not sure either putting everything under the same table is a great way of doing this. would it takes a longer time to load if i only want to see the sell report? compare my table structure and urs.

Jörgen Andersson 13-Aug-13 2:41am

If the table is properly indexed you will hardly notice any difference, and for everything else it will be faster.
Which indexes you need will be depending on what queries you will use, but a composite index on prtType and PrtID seems probable.
Which queries will be the most important ones?

Solution 3

The below solution will hold good for data provided as in the mentioned query.

SQL

Select P.prtID
,ISNULL(P.prtQty,0) + ISNULL(IOS.IOQty,0) - ISNULL(IOO.IOQty,0) - ISNULL(S.sellQty,0) - ISNULL(R.rejQty,0) 'Balance'

--,ISNULL(P.prtQty,0) prtQty , ISNULL(IOS.IOQty,0)IOS_IOQty
--,ISNULL(IOO.IOQty,0) IOO_IOQty, ISNULL(S.sellQty,0) sellQty, ISNULL(R.rejQty,0)rejQty
from T_Product P
Left Join T_stockInOut IOS ON P.prtID = IOS.prtID AND IOS.prtType = 'I'
Left Join T_stockInOut IOO ON P.prtID = IOO.prtID AND IOO.prtType =  'O'
Left Join T_Sell S ON P.prtID = S.prtID
Left Join T_Reject R ON P.prtID = R.prtID

The result is as below

VB

prtID   Balance
1   3
2   0
3   4

Posted 11-Aug-13 19:10pm

Gauri Chodanker

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-08-11T05:22:00

Do i simplified your query? I'm not sure... ;)

SQL

DECLARE @product TABLE (prtID INT IDENTITY(1,1), prtQty INT)
INSERT INTO @product (prtQty)
SELECT 1
UNION ALL SELECT 4
UNION ALL SELECT 4
 
DECLARE @stockInOut TABLE (IOID INT IDENTITY(1,1), prtType VARCHAR(2), prtID INT, IOQty INT, IODate DATETIME) --(I=In, O=Out)
INSERT INTO @stockInOut (prtType, prtID, IOQty, IODate)
SELECT 'I', 1, 6, '2013-08-07'
UNION ALL SELECT 'O', 2, 1, '2013-08-08'
UNION ALL SELECT 'O', 1, 1, '2013-08-08'
 
DECLARE @sell TABLE (sellID INT IDENTITY(1,1), prtID INT, sellQty INT, sellDate DATETIME)
INSERT INTO @sell (prtID, sellQty, sellDate)
SELECT 1, 2, '2013-08-05'
UNION ALL SELECT 2, 2, '2013-08-10'
 
DECLARE @reject TABLE (rejID INT IDENTITY(1,1), prtID INT, rejQty INT, sellDate DATETIME)
INSERT INTO @reject (prtID, rejQty, sellDate)
SELECT 1, 1, '2013-08-09'
UNION ALL SELECT 2, 1, '2013-08-10'


--Balance = prtQty + IOQty(prtType=I) - IOQty(prtType=O) - sellQty - rejQty
--last query
SELECT prtID, SUM(Balance) AS Balance
FROM (
        --second query
	SELECT prtID, MAX(prtQty) + MAX(IQty) - MAX(COALESCE(OQty,0)) - MAX(COALESCE(sellQty,0)) - MAX(COALESCE(rejQty,0)) AS Balance
	FROM (
		--first query
		SELECT P.prtID, P.prtQty, CASE WHEN SIO.prtType = 'I' THEN SIO.IOQty ELSE 0 END AS IQty,
				CASE WHEN SIO.prtType = 'O' THEN SIO.IOQty ELSE 0 END AS OQty, S.sellQty, R.rejQty, SIO.IODate
		FROM @product AS P 
			LEFT JOIN @stockInOut AS SIO ON P.prtID = SIO.prtID
			LEFT JOIN @sell AS S ON S.prtID = SIO.prtID
			LEFT JOIN @reject AS R ON R.prtID = SIO.prtID
		) AS T
		WHERE IODate BETWEEN '2013-08-01' AND '2013-08-31'	OR IODate IS NULL
		GROUP BY prtID
) AS D
GROUP BY prtID
ORDER BY prtID

Result:

prtID   Balance
1	3
2	0
3	4

melvintcs · Accepted Answer · 2013-08-12T21:34:00

Thank you for all the answer, after seeing the answer provided by Jörgen Andersson, it gives me an idea and i decided to go with this solution:

SQL

SELECT prt_ID, SUM(prt_qty) AS balance, DATE(tDate) FROM(
SELECT prtID, prtQty, IODATE FROM product
UNION ALL 
SELECT prtID, IOQty, IODate FROM stockInOut WHERE prtType = 'I' 
UNION ALL 
SELECT prtID, CONCAT('-', IOQty), IODATE FROM stockInOut WHERE prtType = 'O'
UNION ALL 
SELECT prtID, CONCAT('-', sellQty), sellDate FROM sell
UNION ALL 
SELECT prtID, CONCAT('-', rejQty), rejDate FROM reject
) AS A
WHERE DATE(tDate) BETWEEN '2013-08-01' AND '2013-08-31'
GROUP BY prt_ID

the reasons why is that it's easy for me to maintenance the table, and more readable. by combining 4 tables into 1 also is a great idea, but it's not suitable at my case, it has around 30-35 fields if i using that approach, i need to keep scrolling left to right to read the record, and i don't like that. :)
thanks again for all, +5 has been given.

JOIN 4 tables, how to simplify this?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0