Need to add a calculated field using T-SQL

Question

I have a Query that I would like to add a Calculated field to. I need to add Projected Sales for 2013. The calculation would be the current count of months divided by the total year's sales times 12. I have a field for FiscalMonthNum that is not in the query yet. Can someone please show me how I could add the field I need?

SELECT
    a.Vendor,
    vn.ACNAME AS Vendor_Name,
    a.FiscalYear,
    a.QtySold,
    a.ExtCost
FROM
    dbo.S2K_VEND vn
    INNER JOIN
(SELECT
    sd.IFPRVN AS Vendor,
    fc.FiscalYear,
    SUM(sd.SBQSHP) AS QtySold,
    SUM(sd.SBEPRC) AS ExtCost
FROM
    dbo.SalesData sd
    LEFT OUTER JOIN dbo.FiscalCalendar fc ON fc.FiscalDate = sd.SBINDT
WHERE
    sd.SBTYPE = 'O'
AND
    sd.SBINDT > '2011-12-31'
AND
    sd.SBCLS NOT IN ('1500')
GROUP BY
    sd.IFPRVN,
    fc.FiscalYear
)a
ON vn.ACVEND = a.Vendor
GROUP BY
    a.Vendor,
    vn.ACNAME,
    a.FiscalYear,
    a.QtySold,
    a.ExtCost

from which table does `FiscalMonthNum` come from? – T I Jun 07 '13 at 21:39 — T I, Jun 07 '13 at 21:39

score 0 · Answer 1 · answered Jun 07 '13 at 21:52

Would the following do the trick? Dividing the sales by the maximum month number and then multiplying by 12?

SELECT
    a.Vendor,
    vn.ACNAME AS Vendor_Name,
    a.FiscalYear,
    a.QtySold,
    a.ExtCost,
    a.PredictedQtySold,
    a.PredictedExtCost
FROM
    dbo.S2K_VEND vn
    INNER JOIN
(SELECT
    sd.IFPRVN AS Vendor,
    fc.FiscalYear,
    12 * (SUM(sd.SBQSHP)/MAX(FiscalMonthNumber)) AS PredictedQtySold,
    12 * (SUM(sd.SBEPRC)/MAX(FiscalMonthNumber)) AS PredictedExtCost,
    SUM(sd.SBQSHP) AS QtySold,
    SUM(sd.SBEPRC) AS ExtCost
FROM
    dbo.SalesData sd
    LEFT OUTER JOIN dbo.FiscalCalendar fc ON fc.FiscalDate = sd.SBINDT
WHERE
    sd.SBTYPE = 'O'
AND
    sd.SBINDT > '2011-12-31'
AND
    sd.SBCLS NOT IN ('1500')
GROUP BY
    sd.IFPRVN,
    fc.FiscalYear
)a
ON vn.ACVEND = a.Vendor
GROUP BY
    a.Vendor,
    vn.ACNAME,
    a.FiscalYear,
    a.QtySold,
    a.ExtCost
;

Do you mean something like: PredictedExtCost = CASE WHEN fc.FiscalYear = '2013/2014' THEN 12 * (SUM(sd.SBEPRC)/MAX(FiscalMonthNumber)) ELSE NULL END — Danny D, Jun 08 '13 at 08:36

Need to add a calculated field using T-SQL

1 Answers1