Client Statement 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 2005 Forums

Transact-SQL (2005)

Client Statement Query

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-27 : 07:35:26

Hi all - having a prob trying to figure this one out! It's an accounts aplication and I need to produce a monthly statement for clients which will be mailed automatically at the end of the month...

I have 3 tables (simplified below):

Invoice
invoiceid int
invoicedate datetime
clientid int
subtotal decimal(19,2)
paid int

Credit
creditid int
creditdate datetime
clientid int
subtotal decimal(19,2)
paid int

Payments
paymentid int
paymentdate datetime
amount decimal(19,2)
allocated int

The report I need to generate has the client's details at the top and then a list of unpaid invoices, credits and unallocated payments - all sorted by date - with the following columns (sample data supplied)

TYPE|DATE|OUR REF|CREDIT|DEBIT
Invoice | 01-mar-2008 | Inv01 | | £100.00
Credit | 02-mar-2008 | Cre23 | £50 |
Payment | 03-mar-2008 | P04 | £40 |

Would be really grateful for any help!

Thanks in advance,

Stephen.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-27 : 09:22:01

try this

Select 'Invoice',..columns from Invoice
union all
Select 'Credit',..columns from Credit
union all
Select 'Payment',..columns from Payments

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 12:01:48

[code]SELECT t.Type,
t.Date,
.............
FROM
(
Select 'Invoice' AS Type,invoicedate as Date,..columns from Invoice
union all
Select 'Credit' AS Type,creditdate as Date..columns from Credit
union all
Select 'Payment' AS Type,paymentdate as Date,..columns from Payments
)t
ORDER BY t.Date[/code]

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-27 : 14:19:29

Thanks a lot guys - UNION ALL did the job perfectly!

Stephen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources