Currency Fromatting

Hi Everyone,
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:

> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>

Cumulative log

Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.

thoughts?

thanks

What do you mean "cumulative"? If you use a table to store your error records, you can pull in that table and send the records in an e-mail.

Though, if the records are going to be high, you may want to pull the records out to a text file and then attach that file in the Send Mail task. A better approach would be to house everything in a table and write a report against it.|||

how do you pull in different tables? via sql task? and what if you have more 1 table? you would only want to send one email with all the bad records from different tables.

|||Just create one error table. Log all of the bad records to that table and then later select from that table.|||

well all tables have different structures so a single table would not work. any other ideas?

|||If you want to combine them, you are going to have to make the structures similar at some point, right? You could use a dataflow with multiple sources to combine the tables into a flat file, then email that as an attachment.|||

I don't see an straight forward way to get all those rows into a single file/email. Perhaps, it may be easier to include just the key column(s); in such way the structure is the same for all the tables.

Ctrl+Tab not working

Having an issue with the navigation keys. When I first installed Studio I could Hold Ctrl and tab through the active windows, then on releasing the buttons I would move to that active window. Now when I press Ctrl+Tab the list will come up, but wont jump to the screen I end on. The quick link window will stay up and the only way I can transfer windows is hitting Enter or clicking on the desired window with my mouse. Any ideas on this "seemingly simple" issue. I went through all of the Tools -> Options window with no luck.Moving to the tools group.