How to get monthly YTD data

  • Normally I do too, but they were in the original example. I was more interested in people's thoughts on using CROSS APPLY instead of a CTE.

  • zuber.patel (1/24/2012)


    Hi Rookie,

    The Article is really helpful, just want to know how we can display data in horizontal form like

    Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11

    UserYTD6253339505564161274281

    Two examples I just made. One using PIVOT and the other using conditional sum:

    IF OBJECT_ID('tempdb.dbo.#TotalSales', 'U') IS NOT NULL

    DROP TABLE dbo.#TotalSales;

    GO

    CREATE TABLE dbo.#TotalSales

    (

    SaleDate DATETIME PRIMARY KEY,

    SaleProfit DECIMAL(19,2)

    );

    GO

    INSERT INTO dbo.#TotalSales(SaleDate, SaleProfit)

    VALUES ('20110105', 34000), ('20110820', 50045.60), ('20111003', 13650.32);

    GO

    /* =======================================

    Example 1: USING PIVOT

    ---------------------------------------

    */

    WITH PivotTable AS

    (

    SELECT (SELECT TOP(1) YEAR(#TotalSales.SaleDate)

    FROM dbo.#TotalSales

    WHERE #TotalSales.SaleDate IS NOT NULL) AS Year,

    Months.M,

    ISNULL(#TotalSales.SaleProfit, 0) AS SalesProfit

    FROM (SELECT 'Jan' AS M UNION ALL SELECT 'Fev' UNION ALL SELECT 'Mar' UNION ALL SELECT

    'Apr' UNION ALL SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT

    'Jul' UNION ALL SELECT 'Aug' UNION ALL SELECT 'Sep' UNION ALL SELECT

    'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dez') AS Months

    LEFT JOIN dbo.#TotalSales ON Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)

    )

    SELECT Year, Jan, Fev, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dez

    FROM (SELECT Year, M, SalesProfit

    FROM PivotTable) AS P

    PIVOT(SUM(SalesProfit) FOR M IN (Jan, Fev, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dez)) AS X;

    /* =======================================

    Example 2: NOT USING PIVOT

    ---------------------------------------

    */

    WITH NonPivotTable AS

    (

    SELECT (SELECT TOP(1) YEAR(#TotalSales.SaleDate)

    FROM dbo.#TotalSales

    WHERE #TotalSales.SaleDate IS NOT NULL) AS Year,

    Months.M,

    ISNULL(#TotalSales.SaleProfit, 0) AS SalesProfit

    FROM (SELECT 'Jan' AS M UNION ALL SELECT 'Fev' UNION ALL SELECT 'Mar' UNION ALL SELECT

    'Apr' UNION ALL SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT

    'Jun' UNION ALL SELECT 'Aug' UNION ALL SELECT 'Sep' UNION ALL SELECT

    'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dez') AS Months

    LEFT JOIN dbo.#TotalSales ON Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)

    )

    SELECT Year AS Year,

    SUM(CASE M WHEN 'Jan' THEN SalesProfit ELSE 0 END) AS Jan,

    SUM(CASE M WHEN 'Fev' THEN SalesProfit ELSE 0 END) AS Fev,

    SUM(CASE M WHEN 'Mar' THEN SalesProfit ELSE 0 END) AS Mar,

    SUM(CASE M WHEN 'Apr' THEN SalesProfit ELSE 0 END) AS Apr,

    SUM(CASE M WHEN 'May' THEN SalesProfit ELSE 0 END) AS May,

    SUM(CASE M WHEN 'Jun' THEN SalesProfit ELSE 0 END) AS Jun,

    SUM(CASE M WHEN 'Jul' THEN SalesProfit ELSE 0 END) AS Jul,

    SUM(CASE M WHEN 'Aug' THEN SalesProfit ELSE 0 END) AS Aug,

    SUM(CASE M WHEN 'Sep' THEN SalesProfit ELSE 0 END) AS Sep,

    SUM(CASE M WHEN 'Oct' THEN SalesProfit ELSE 0 END) AS Oct,

    SUM(CASE M WHEN 'Nov' THEN SalesProfit ELSE 0 END) AS Nov,

    SUM(CASE M WHEN 'Dez' THEN SalesProfit ELSE 0 END) AS Dez

    FROM NonPivotTable

    GROUP BY Year;

    I'm not sure how it would deal with a huge table since I'm joining my table with the tally table using "Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)". And I'm pretty sure that that subquery to get the year will not be great for performance since the execution plan show it takes 21% of it.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • And a more simplified version of Example 2 below: :blush:

    SELECT YEAR(SaleDate) AS Year,

    SUM(CASE MONTH(SaleDate) WHEN 1 THEN SaleProfit ELSE 0 END) AS Jan,

    SUM(CASE MONTH(SaleDate) WHEN 2 THEN SaleProfit ELSE 0 END) AS Fev,

    SUM(CASE MONTH(SaleDate) WHEN 3 THEN SaleProfit ELSE 0 END) AS Mar,

    SUM(CASE MONTH(SaleDate) WHEN 4 THEN SaleProfit ELSE 0 END) AS Apr,

    SUM(CASE MONTH(SaleDate) WHEN 5 THEN SaleProfit ELSE 0 END) AS May,

    SUM(CASE MONTH(SaleDate) WHEN 6 THEN SaleProfit ELSE 0 END) AS Jun,

    SUM(CASE MONTH(SaleDate) WHEN 7 THEN SaleProfit ELSE 0 END) AS Jul,

    SUM(CASE MONTH(SaleDate) WHEN 8 THEN SaleProfit ELSE 0 END) AS Aug,

    SUM(CASE MONTH(SaleDate) WHEN 9 THEN SaleProfit ELSE 0 END) AS Sep,

    SUM(CASE MONTH(SaleDate) WHEN 10 THEN SaleProfit ELSE 0 END) AS Oct,

    SUM(CASE MONTH(SaleDate) WHEN 11 THEN SaleProfit ELSE 0 END) AS Nov,

    SUM(CASE MONTH(SaleDate) WHEN 12 THEN SaleProfit ELSE 0 END) AS Dez

    FROM dbo.#TotalSales

    GROUP BY YEAR(SaleDate);

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • codebyo (1/24/2012)


    And a more simplified version of Example 2 below: :blush:

    SELECT YEAR(SaleDate) AS Year,

    SUM(CASE MONTH(SaleDate) WHEN 1 THEN SaleProfit ELSE 0 END) AS Jan,

    SUM(CASE MONTH(SaleDate) WHEN 2 THEN SaleProfit ELSE 0 END) AS Fev,

    SUM(CASE MONTH(SaleDate) WHEN 3 THEN SaleProfit ELSE 0 END) AS Mar,

    SUM(CASE MONTH(SaleDate) WHEN 4 THEN SaleProfit ELSE 0 END) AS Apr,

    SUM(CASE MONTH(SaleDate) WHEN 5 THEN SaleProfit ELSE 0 END) AS May,

    SUM(CASE MONTH(SaleDate) WHEN 6 THEN SaleProfit ELSE 0 END) AS Jun,

    SUM(CASE MONTH(SaleDate) WHEN 7 THEN SaleProfit ELSE 0 END) AS Jul,

    SUM(CASE MONTH(SaleDate) WHEN 8 THEN SaleProfit ELSE 0 END) AS Aug,

    SUM(CASE MONTH(SaleDate) WHEN 9 THEN SaleProfit ELSE 0 END) AS Sep,

    SUM(CASE MONTH(SaleDate) WHEN 10 THEN SaleProfit ELSE 0 END) AS Oct,

    SUM(CASE MONTH(SaleDate) WHEN 11 THEN SaleProfit ELSE 0 END) AS Nov,

    SUM(CASE MONTH(SaleDate) WHEN 12 THEN SaleProfit ELSE 0 END) AS Dez

    FROM dbo.#TotalSales

    GROUP BY YEAR(SaleDate);

    I was just going to replay with that as a better version 🙂

  • tfendt (1/24/2012)


    I was just going to replay with that as a better version 🙂

    I deserve to be punched for that.

    I was trying to destroy a mosquito with a nuclear weapon. 😀

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • TheSQLGuru (1/24/2012)


    2) cursors are still the fastest supported way to do running totals in sql server. Jeff Moden and others have come up with the "quirky update" to do these as well, although not officially supported by microsoft.

    Hugo Kornelis' set-based iteration method from the first MVP Deep Dives book is generally much faster than a T-SQL cursor. Running totals using SQLCLR (which some people refer to incorrectly as a cursor) are a fraction behind that. In the very specific cases Quirky Update is aimed at (persisting the running total in the same table exploiting an existing clustered index), I have never seen anything beat it. I have always meant to test parallel-SQLCLR versus QU on a very large set, but there are many things I would like to test 'one day'.

  • SQL Kiwi (1/24/2012)


    TheSQLGuru (1/24/2012)


    2) cursors are still the fastest supported way to do running totals in sql server. Jeff Moden and others have come up with the "quirky update" to do these as well, although not officially supported by microsoft.

    Hugo Kornelis' set-based iteration method from the first MVP Deep Dives book is generally much faster than a T-SQL cursor. Running totals using SQLCLR (which some people refer to incorrectly as a cursor) are a fraction behind that. In the very specific cases Quirky Update is aimed at (persisting the running total in the same table exploiting an existing clustered index), I have never seen anything beat it. I have always meant to test parallel-SQLCLR versus QU on a very large set, but there are many things I would like to test 'one day'.

    I agree to those things Paul, but those other methods are also not very well known in the real world either. pCLR would be neat to trot out, but with 2012 coming I am holding out hope of better things. I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (1/24/2012)


    . . . I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂

    2012 can handle 64 cores of the Haswell chip, which will be able to be overclocked to 4.2 GHz. Make your call on whether that is "as fast as [they] can be."

  • TheSQLGuru (1/24/2012)


    I agree to those things Paul, but those other methods are also not very well known in the real world either.

    This is why I mention them so much 😉

    pCLR would be neat to trot out, but with 2012 coming I am holding out hope of better things. I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂

    It'll certainly be faster than triangular joins and T-SQL cursors, yes. It's a shame the built-ins aren't faster than (serial) CLR or set-iteration from the word go, but there we are. The addition of e.g. SUM (ORDER BY) is a big win for 2012 nevertheless.

  • In Australia our financial year runs from July to June so in our case YTD means start counting from 1st July ...

    Select SaleDate, right(Cast(100+Month(SaleDate) as char(3)),2)+'/'+Cast(Year(SaleDate) as Char(4)) as Period,

    (Year(SaleDate)-2000)*12+Month(SaleDate) as Months,

    (Case When Month(SaleDate)>6 then Year(SaleDate) else Year(SaleDate)-1 end) as FinYear,

    (Case When Month(GetDate())>6 then Year(GetDate()) else Year(GetDate())-1 end) as CurFinYear

    from SalesDetails

    Where FinYear=CurFinYear

    In this case

    SaleDate is the Date of Sale eg 04/10/2011

    Period is a text field to display for month and year eg 10/2011 (but would be better with the month is words as per the original example)

    Months is the number of months since 01/01/2000 eg 11*12+10 = 154 - used for sorting the periods convering motre than one calendar year

    Finyear is the financial year to which the sale date belongs

    CurFinyear is the current financial year based on today's date

    So if FinYear=CurFinyear then it's the current financial year ( eg 04/10/2011 is part of the year from 01/07/2011 to 30/06/2012 or what I'm calling the 2011 financial year)

    If FinYear=CurFinyear-1 then it's the preivous financial year (so I can compare last YTD with trhis YTD)

    Actually I store the Finyear, Months and CurFinyear as calculated fields in my salesdetails table so I don't have to calculate them in every report I generate from these details.

  • Thanks Mike... 🙂

    Anjali Gautam

  • to TheSQLGuru

    1) Solution architect must have knowledge about SSAS, therefore there must be no such task for MS SQL Server db developer

    2) who says we need to query raw data? - transform it (OLTP -> DW -> DM -> OLAP cube) and query a cube. There is standard Year-to-date MDX functions.

    3) functions prevent use of indexes. Indexes' usage is necessary to speed up query.

    4) Again Solution architect must have knowledge about SSIS, and SSIS doesnt perform any output - it s ETL tool. It is "arrow" on the route of data (see issue #2). Use any MDX client (ADOMD.Net,Excel,etc.) at front end to output data instead.

    5)

    a)Yet Again Solution architect must have knowledge about SSRS

    b)it s part of SQL Server anyway, and it s the task of 5-15 minutes to create dataset on T-SQL/MDX query, put tablix at report, and perform some simple formatting. And again there is ADO.Net/ADOMD.Net /Excel to use query results in client applications at least.

    c) which year has 5M months? or just report with 5M rows? It s kind of another example of bad design. SSRS shouldn't receive more data rows than it must visualize - there is GROUP BY clause in T-SQL for this at least. I did a swarm of such "report optimization" when I had just to add GROUP BY clause to datasets' queries.

    Briefly,

    Each component of MS SQL Server must do the job it was designed for

    T-SQL - OLTP

    SSIS - data preparing

    SSAS - reporting, aggregation and analytical processing

    SSRS - data visualization.

    Actually I do good money for things I tell you here 😉

  • This is the type of problem that MDX handles quite well, if the user has an OLAP cube to query from. A date dimension and a fact table is all you need.

    WITH [measures].[year to date sales] AS

    aggregate(

    periodsToDate( [date].[calendar].[calendarYear], [date].[calendar].currentMember),

    [measures].[salesAmount]

    )

    Select {[measures].[salesAmount], [measures].[year to date sales]} on columns,

    {[date].[calendar].[months].memebers} on rows

    From user_cube

    There is even a YTD function in analysis services that can perform this function. My point here is that as a long term solution, an olap cube is worth exploring since this is the type of report that is quite insightful to a business (and not particularly difficult to create) .

    ----------------------------------------------------

Viewing 13 posts - 31 through 43 (of 43 total)

You must be logged in to reply to this topic. Login to reply