Reporting non existant fields - 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

General SQL Server Forums

New to SQL Server Programming

Reporting non existant fields

Author

Topic

cayosonia
Starting Member

3 Posts

Posted - 2009-09-15 : 11:24:14

I've had a search around and I am sorry if this has already been asked. I am trying to write a report for a loan database; I have one table with customer information and I have a table with payments. I want to write a query which shows what the customers have paid. This is easy enough if the customers paid something over the last month, if they haven't they don't show up, using a simple query such as

Select c.listid, c.fullname, c.agreedpayment, p.date, p.amount
from customers c, payments p
Where c.listid = p.customerlistid
and datediff(month, p.date, getdate()) < 1

What I would like to show in my reports is a zero balance for those customers who don't have payments in the payment table.

I am a bit rusty on T-SQL, is this possible? Thanks for having a think for me

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 11:43:14

This will have at least 1 row for all customers. It may have multiple rows per customer if there are multiple payment rows per customer.


Select c.listid
       ,c.fullname
       ,c.agreedpayment
       ,p.date
       ,isNull(p.amount, 0)
from   customers c
left outer join payments p
       on p.customerlistid = c.listid
       and datediff(month, p.date, getdate()) < 1

Be One with the Optimizer
TG

cayosonia
Starting Member

3 Posts

Posted - 2009-09-15 : 11:59:14

Thanks TG - I will check it out :-)

cayosonia
Starting Member

3 Posts

Posted - 2009-09-15 : 13:21:17

Thanks again TG this produced the result I wanted, Have been banging my head on the desk with IF EXISTS and NOT EXISTS. This is so much easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources