Writing Sql Query for three joins

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

General SQL Server Forums

New to SQL Server Programming

Writing Sql Query for three joins

Author

Topic

niku4u
Starting Member

2 Posts

Posted - 2010-12-09 : 14:36:10

I am facing some problem writing a query. I have three tables which I need to Join for this query. Following are those tables and example data in them.

Invoice Table


invcKey	balance CustKey	 TranDate
1	500	    10	 1/1/2010
2	50	    10	 1/5/2010
3	0	    10	 1/15/2010
4	-100	    10	 1/17/2010


InvoiceDetails Table
InvcKey	itemKey
1	123
1	125
2	126
2	127
3	124
4	126


Inventory Table
itemKey	itemType
123	S
124	M
125	L
126	XL
127	XL

Now From above tables I want to display custKey who has outstanding balance owe to my company. I also need to consider any credit my company owes to this customer. And we run report for 15 days. We have 4 ItemType. XL itemtype always has separate invoice and remaining ItemTypes i.e S,M and L have one Invoice. So we need to consider balance only for those invoice which contains S,M and L itemtypes.

So as example if i want to display custKey who owes to company between date 1st January to 15th January.

The result would be like
CustKey Amount
10 400

Here As we can see that though -100 credit outside of the range still we are considering and Customer has one more balance 50 for InvcKey 2 but as I said if ItemType is XL and we don't need to count those outstanding balances.

I know its long discription and might be confusing .. but I really need some help on this.. Please let me know if you have any questions..

If someone can guide me on that. Thanks In advance..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-09 : 18:03:34

So even though the -100 is of item type XL, it is still used in the calculation. Is there some reason that a negative balance would NOT be considered (outside some date range or anything)?

niku4u
Starting Member

2 Posts

Posted - 2010-12-10 : 09:04:38

We are considering any credit we owe to customer. it does not matter if it is in range or out of range. hence on above calculation we deduct 100 from total amount(500) customer owe to us.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 00:42:23

may be this?


SELECT *
FROM
(
SELECT CustKey,
TotalBalance - (SELECT SUM(balance) FROM table WHERE CustKey=t.CustKey AND TranDate> t.Latest AND balance< 0 ) AS outbal
FROM
(
SELECT i.CustKey,SUM(i.balance) AS TotalBalance,MAX(i.TranDate) AS Latest
FROM Invoice i
INNER JOIN InvoiceDetails id
ON id.InvcKey = i.InvcKey
INNER JOIN Inventory inv
ON inv.itemKey = id.itemKey
WHERE i.TranDate BETWEEN '1/1/2010' AND '1/15/2010'
AND inv.itemType IN ('S','M','L')
GROUP BY i.CustKey
)t
)r
WHERE r.outbal >0

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources