GUYS HELP!!! IM ON A DEADLINE - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

GUYS HELP!!! IM ON A DEADLINE

Author

Topic

twinklestar0802
Starting Member

17 Posts

Posted - 2005-05-18 : 13:45:29

hey guys... i need ur help... im having problem in combining 4 tables... im going to make a sales report that access 4 table:
products(productid, ...)
returndetails(returnid,productid, quantity....)
cashinvoicedetails(cashinvoiceid, productid, quantity)
creditinvoicedetails(creditinvoice, productid, quantity)

i want to sum all quantities from the cashinvoicedetails and creditinvoicedetails, grouped by productid munis the
sum of returned quantities from the returndetails grouped by productid.... however i want all products in the products table to appear even if they do not have any record in the returndetails, cashinvociedetails and creditinvoicedetails table.. please help me!!!!!!!!!!!

the result for the sales report will actually be:

productid (qty in cashinvoice+qty in crditinvoice) -( qty in returns)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 15:01:24

Have a look at left outer joins

select p.productid, coalesce(c.quantity,0) - coalesce(r.quantity, 0)
products p
left join (select productid, sum(quantity) from cashinvoicedetails group by productid) c
on c.productid = p.productid
left join (select productid, sum(quantity) from returndetails group by productid) r
on r.productid = p.productid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

twinklestar0802
Starting Member

17 Posts

Posted - 2005-05-20 : 03:18:41

thank u for the fast reply!!!!!!!!!!! i had an error... it says error near p, r, c... im using mssql... why am i having this error?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 03:52:02

I missed out the from before products.
Are you a bit out of your depth here?
Maybe you should have a chat with your manager about what you have been given to do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources