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.
| Author |
Topic |
|
jonathan.crockett
Starting Member
4 Posts |
 Posted - 2009-04-06 : 07:36:31
|
| I have a view which lists all invoices. The columns it shows are; file code, invoice type code, invoice amount, invoice date, invoice number.I require for a report (in reporting services) to show two different tables. One showing each file which has had any invoices sent within the calendar year and the total of those invoices. The second table shows the same but files which have been invoiced only in that quarter. Both tables have the same columns.The problem arises as we are dealing with situations where some files are liable for VAT and some are not. In the view the “invoice type code” column holds if the invoice is in regards to “Fees”, “Costs” or “VAT”. We need to have a row for each file that was invoiced in the appropriate period, be that invoice for fees, costs or VAT (if liable). In that row there is a field which shows the total invoiced that period, Fees+Costs+VAT (if liable). Whether the VAT is liable or not is specified on a file per file basis by a separate table.We currently specify the period using a calendar table that links to the “invoice date” column and variables for the year and quarter, which are passed in when the report is run.I know this might be clear as mud, so I’ll try and clear up anything if anyone who is good enough to help has questions. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-06 : 07:50:25
|
| Can you post some example data with the exact output you require? Please post enough examples to illustrate all the situations you need to cover. It would also help if you posted the DDL (CREATE TABLE) code for the table that contains the VAT info, and how that joins to the view you're using. |
 |
|
|
|
|
|
Working out fees in a year and in a quarter