tags:

views:

75

answers:

3

I've created a view, using the following select statement.

As you can see, i've aliased a lot of the columns to make it far more friendly.

I need to return a column on the end of this that is "GrandTotal" and is basically SubTotal + VAT (this VAT column is displayed as a percentage, so need to add % to this)

Thanks for your help.

SELECT     
    No_ AS CroCode, 
    Description, 
    [Vendor Item No_] AS SupplierStockCode,
    [Qty_to Receive] AS Qty,
    [Unit Cost (LCY)] AS UnitPrice,
    [VAT %] AS VATPercent,
    ROUND([Unit Cost (LCY)] * [Qty_ to Receive], 2) AS SubTotal

FROM
    dbo.TableNameGoesHere
A: 

Try char(VAT) || '%' AS VATPercent,

Aaron Digulla
A: 

I'm not sure whether this rounding is correct (does VAT round down or to the nearest penny?), but do you mean something like:

ROUND((1+VAT/100) * ROUND([Unit Cost (LCY)] * [Qty_ to Receive], 2),2) AS GrandTotal
Ruffles
A: 

Would this work

Declare @TableNameGoesHere Table 
(
    [No_] VarChar (30),
    Description VarChar (30),  
    [Vendor Item No_] VarChar (30),
    [Qty_ to Receive] int,
    [Unit Cost (LCY)] float,
    [VAT %] float
)

Insert into @TableNameGoesHere Values ('1x', '1or3m Ipsum', '231234sxsd', 12, 23.36, 3.3)
Insert into @TableNameGoesHere Values ('2y', '2or43 Ipsum', '23vbswsxsd', 23, 13.86, 3.3)
Insert into @TableNameGoesHere Values ('3h', '3or46 Ipsum', 'asdf757xsd', 13, 43.55, 3.3)
Insert into @TableNameGoesHere Values ('4r', '4or6m Ipsum', '908msn2341', 22, 73.12, 3.3)

SELECT     
    No_ AS CroCode, 
    Description, 
    [Vendor Item No_] AS SupplierStockCode,
    [Qty_ to Receive] AS Qty,
    [Unit Cost (LCY)] AS UnitPrice,
    [VAT %] AS VATPercent,
    ROUND([Unit Cost (LCY)] * [Qty_ to Receive], 2) AS SubTotal,
    Convert (VarChar, [VAT %] * ROUND([Unit Cost (LCY)]/100 * [Qty_ to Receive], 2) + ROUND([Unit Cost (LCY)] * [Qty_ to Receive], 2) ) + ' %' AS GrandTotal

FROM
    @TableNameGoesHere
Raj More