SQL Server 2008 – Getting Today’s, MTD, YTD Sales and Profit

sql-server-2008

I have a database which give me sale and profit for each date. Schema looks like following :

Date         Sales      Profit

2015-03-07   133333     12348
2015-03-08   214563     34545
2015-03-09   299593     59940

Now I want to get results like following:

      Today    MTD     YTD

Sales

Profit

The brute-force approach I am using have 6 sub-queries with union.

It looks like following:

  SELECT 'Sales', TotalSales, 0, 0 
  FROM SalesTable
  where Date = GETDATE()
  UNION
  SELECT 'Sales',0, SUM(TotalSales), 0 
  FROM SalesTable
  where MONTH(Date) = MONTH(GETDATE())
  UNION
  SELECT 'Sales',0,0, SUM(TotalSales) 
  FROM SalesTable
  where YEAR(Date) = YEAR(GETDATE())

and the similar for profit.

How can this be done in a more efficient/structural way?

Best Answer

Mocked up some data and used a couple of CTEs to generate the aggregates. Similar to creating a bunch of separate queries joined or unioned however you wanted. This provides a little more readability and flexibility. I also added a variable to simulate running this for different dates.

    /*  Data mockup

    IF OBJECT_ID('SalesTable') IS NOT NULL 
        DROP TABLE SalesTable;

    CREATE TABLE SalesTable
    (
          [Date] DATE
        , Sales  NUMERIC(19,4)
        , PROFIT NUMERIC(19,4)
    );

    INSERT SalesTable
    VALUES  
           ('2014-12-07', 133333, 12348)
         , ('2015-03-07', 133333, 12348)
         , ('2015-03-08', 214563, 34545)
         , ('2015-03-09', 299593, 59940)
         , ('2015-05-07', 233333, 12348)
         , ('2015-05-08', 214563, 34545)
         , ('2015-05-09', 299593, 59940)
         , ('2015-09-07', 333333, 12348)
         , ('2015-09-08', 214563, 34545)
         , ('2015-09-09', 299593, 59940);
    */

    DECLARE @salesDT DATE;

    --SIMULATES PASSING IN A DATE TO AN SP
    --SET @salesDT = '2014-12-07';

    --SIMULATES A NULL BEING PASSED IN AND USING TODAY
    SET @salesDT = ISNULL(@salesDT, CONVERT(DATE,GETDATE()));

    WITH 
    cteMonthlySales AS
    (
        SELECT MONTH([Date]) AS SalesMonth, SUM(Sales) MonthlySales
        FROM SalesTable
        WHERE MONTH([Date]) = MONTH(@salesDT)
        GROUP BY MONTH([Date])
    ),   
    cteAnnualSales AS
    (
        SELECT YEAR([Date]) AS SalesYear, SUM(Sales) AS AnnualSales
        FROM SalesTable
        WHERE YEAR([Date]) = YEAR(@salesDT)
        GROUP BY YEAR([Date])
    )
    SELECT CAST(st.Sales AS MONEY) AS Today, CAST(sm.MonthlySales AS MONEY) AS MTD, CAST(ym.AnnualSales AS MONEY) AS YTD
    FROM SalesTable st
        JOIN cteMonthlySales sm ON sm.SalesMonth=MONTH(st.[Date])
        JOIN cteAnnualSales ym ON ym.SalesYear=YEAR(st.[Date])
    WHERE [Date] = @salesDT;
Related Question