суббота, 13 июля 2013 г.

Как рассчитать продажи по годам и месяцам


-- Выведем результаты по годам и месяцам
SELECT
    YEAR(OH.OrderDate) AS "Год"
    ,MONTH(OH.OrderDate) AS "Месяц"
    ,SUM (OD.LineTotal) AS "Итого"
FROM
    AdventureWorks2012.Sales.SalesOrderDetail AS OD
         INNER JOIN
    AdventureWorks2012.Sales.SalesOrderHeader AS OH
ON
    OD.SalesOrderID = OH.SalesOrderID
GROUP BY
    YEAR(OH.OrderDate), MONTH(OH.OrderDate)
ORDER BY
    YEAR(OH.OrderDate), MONTH(OH.OrderDate)

-- Выведем месяц в текстовом виде
SET LANGUAGE Russian
SELECT
    YEAR(OH.OrderDate) AS "Год"
    ,DATENAME(MONTH, OH.OrderDate) AS "Месяц"
    ,SUM (OD.LineTotal) AS "Итого"
FROM
    AdventureWorks2012.Sales.SalesOrderDetail AS OD
         INNER JOIN
    AdventureWorks2012.Sales.SalesOrderHeader AS OH
ON
    OD.SalesOrderID = OH.SalesOrderID
GROUP BY
    YEAR(OH.OrderDate)
    ,MONTH(OH.OrderDate)
    ,DATENAME(MONTH, OH.OrderDate)
ORDER BY
    YEAR(OH.OrderDate)
    ,MONTH(OH.OrderDate)
-- -- Выведем месяц и год одной строкой
SET LANGUAGE Russian
SELECT
    CAST(YEAR(OH.OrderDate) AS NVARCHAR(50)) + ' год, '
    + DATENAME(MONTH, OH.OrderDate) AS "Месяц"
    ,SUM (OD.LineTotal) AS "Итого"
FROM
    AdventureWorks2012.Sales.SalesOrderDetail AS OD
         INNER JOIN
    AdventureWorks2012.Sales.SalesOrderHeader AS OH
ON
    OD.SalesOrderID = OH.SalesOrderID
GROUP BY
    YEAR(OH.OrderDate)
    ,MONTH(OH.OrderDate)
    ,DATENAME(MONTH, OH.OrderDate)
ORDER BY
    YEAR(OH.OrderDate)
    ,MONTH(OH.OrderDate)