CODEWITHSUNDEEP
sql-servert-sql
tsqln00b
tsqln00b

Reputation: 355

Sales Summary Data by Month and Year in T-SQL

I have Sales data by Month by Year for all my Vendors. I am looking to have the following resulting fields...Vendor, 2012 Sales, 2013 Sales, Average Monthly Sales for 2012, 2013 Monthly Projected Sales. The last field is the one I am having the most trouble with. I need to Average the last 5 months of full Sales data and then multiply that by 12. Here is the query I am working on...

SELECT
    a.Vendor,
    a.Vendor_Name,
    SUM(a.Sales2012) AS Sales_2012,
    SUM(a.Sales2013) AS Sales_2013
FROM
(SELECT
    Vendor,
    Vendor_Name,
    CASE
        WHEN Month_Sold IN ('201201','201202','201203','201204','201205','201206','201207','201208','201209','201210','201211','201212') THEN SUM(Sales_Dlr) END AS 'Sales2012',
    CASE
        WHEN Month_Sold IN ('201301','201302','201303','201304','201305','201306','201307','201308','201309','201310','201311','201312') THEN SUM(Sales_Dlr) END AS 'Sales2013'
FROM
    dbo.SalesSummary
GROUP BY
    Vendor,
    Vendor_Name,
    Month_Sold
)a
GROUP BY
    a.Vendor,
    a.Vendor_Name
ORDER BY
    a.Vendor_Name

Upvotes: 0

Views: 1551

Answers (1)

John Dewey
John Dewey

Reputation: 7093

Assuming SalesSummary.Vendor is an ID, you could try adding a subquery for last 5 months, and then multiply by 12 in the outer query:

DECLARE @Today DATE = GETDATE();

SELECT
    a.Vendor,
    a.Vendor_Name,
    SUM(a.Sales2012) AS Sales_2012,
    SUM(a.Sales2013) AS Sales_2013,
    b.Sales_Dlr_Avg * 12 AS [Monthly Projected Sales]
FROM
(SELECT
    Vendor,
    Vendor_Name,
    CASE
        WHEN Month_Sold IN ('201201','201202','201203','201204','201205','201206','201207','201208','201209','201210','201211','201212') THEN SUM(Sales_Dlr) END AS 'Sales2012',
    CASE
        WHEN Month_Sold IN ('201301','201302','201303','201304','201305','201306','201307','201308','201309','201310','201311','201312') THEN SUM(Sales_Dlr) END AS 'Sales2013'
FROM
    dbo.SalesSummary
GROUP BY
    Vendor,
    Vendor_Name,
    Month_Sold
)a
JOIN (
    SELECT Vendor, Sales_Dlr_Avg = AVG(Sales_Dlr)
    FROM dbo.SalesSummary
    WHERE DATEDIFF(MONTH, CAST(Month_Sold AS VARCHAR(6)) + '01', @Today) BETWEEN 1 AND 5
    GROUP BY Vendor
) b ON b.Vendor = a.Vendor
GROUP BY
    a.Vendor,
    a.Vendor_Name,
    b.Sales_Dlr_Avg
ORDER BY
    a.Vendor_Name;

Upvotes: 1

Related Questions