Accounts Query - 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)

Accounts Query

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2005-07-26 : 05:21:03

Hi all - new to this forum :)

I have a problem that is driving me crazy... I have the following tables:

tblinvoices
tblinvoicedetails
tblorders
tblmonths

I need to produce a yearly turnover for a client - If there are no invoices for that client for a certain month, I still need a zero value. I link tblmonths.monthid to month(tblinvoices.invoicedate) and that is fine, however, when I start to narrow it down by filtering for a client id on the order side, my months dissapear - can any of you tell me what I'm doing wrong?


SELECT     tblmonths.monthid, tblmonths.monthtext, tblorders.customerid
FROM         tblorders RIGHT OUTER JOIN
                      tblinvoices ON tblorders.orderid = tblinvoices.orderid RIGHT OUTER JOIN
                      tblmonths ON MONTH(tblinvoices.invoicedate) = tblmonths.monthid
WHERE     (tblorders.customerid = 113)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-26 : 08:46:36

SELECT tblmonths.monthid, tblmonths.monthtext, tblorders.customerid
FROM tblorders RIGHT OUTER JOIN
tblinvoices ON tblorders.orderid = tblinvoices.orderid
and (tblorders.customerid = 113)
RIGHT OUTER JOIN
tblmonths ON MONTH(tblinvoices.invoicedate) = tblmonths.monthid

==========================================
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.

steve_c
Yak Posting Veteran

54 Posts

Posted - 2005-07-26 : 09:11:28

Thanks for the quick reply - I've had to change this now and use a stored procedure- something which I've never used - would I be right in doing this:

I create a stored procedure:


select
tblorders.orderid,
customerid,
invoiceid,
tblmonths.monthid
into #tempcustomer
from tblmonths
left outer join tblinvoices on MONTH(tblinvoices.invoicedate) = tblmonths.monthid
inner join tblorders
on tblinvoices.orderid = tblorders.orderid
where customerid = @clientid

(this in sql server asks me to declare @clientid but I want to declare that from my vb.net form)

and then put this in my dataset:


SELECT     
tblmonths.monthid, 
tblmonths.monthtext, 
#tempcustomer.customerid, 
SUM(tblinvoicedetails.qty * tblinvoicedetails.price) AS invoicetotal, 
#tempcustomer.invoiceid

FROM tblmonths
left outer join #tempcustomer on #tempcustomer.monthid = tblmonths.monthid
left outer join tblinvoicedetails
on tblinvoicedetails.invoiceid = #tempcustomer.invoiceid

GROUP BY tblmonths.monthid, tblmonths.monthtext, #tempcustomer.customerid, #tempcustomer.invoiceid

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources