How to get monthly YTD data

  • Comments posted to this topic are about the item How to get monthly YTD data

  • wouldn't this actually give you all the data for a given month regardless of which year it occurred?

  • This good techinique is preety simple to apply for something that declarative sql does not handle very well.

    It always surprises inexperienced developers how this simple and rather common request for a report is actually difficult to handle is pure sql code (no stored procedures or reporting tools like Crystal Reports).

    Be aware that if you use it on a large set of data you are likely to blow up your sql server (example customers running totals balances).

    I hear that sql 2012 has a new feature that can calculate running totals but haven't tried it yet.

    Good post

  • what's wrong with just using the datename function ?

    i.e. select datename(mm,getdate()) --retuns a value of January

    and then if you just want the 1st 3 letters add a left to the command

    select left(datename(mm,getdate()),3) --retuns a value of Jan

  • 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

  • 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

    Regards,

    Zuber

  • Did you try pivot() on your data? I think that will do exactly what you want...:w00t:

  • @Zuber: Check out PIVOT, which will do what you're after. http://msdn.microsoft.com/en-us/library/ms177410.aspx

    Alternatively you might be better doing that clientside with a transpose or equivalent, depending on the language.

  • Hi,

    I am trying to run the same query in Oracle, but i am getting error for Convert function.Can you please tell me substitute for convert .

  • Anjali,

    You dont have to have same thing for the dates. You can go with the approach suggested above:

    select datename(mm,getdate()) --retuns a value of January

    and then if you just want the 1st 3 letters add a left to the command

    select left(datename(mm,getdate()),3) --retuns a value of Jan

    You just have to break your date field into months...:-)

    Rookie

  • Thanks Rookie...

  • Thanks OP, THIs a really elegant way of handling a YTD request. However, many DB's such as i have to deal with YTD issues based on a Fiscal YTD such as Feb-1 - Jan31.

    How would you handle this situation?

  • Try this ...

    ;WITH [Period] ([StartDate]) AS (

    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - [number], 0)

    FROM [master]..[spt_values]

    WHERE [type] = 'P'

    AND [number] BETWEEN 0 AND 5

    )

    SELECT

    [Period] = RIGHT(CONVERT(VARCHAR(11), p.[StartDate], 106), 8),

    [UserCount] = u.[UserCount]

    FROM [Period] p

    OUTER APPLY (

    SELECT [UserCount] = COUNT(*)

    FROM [MyUser]

    WHERE [UserCreationDate] < p.[StartDate]

    ) u

    ORDER BY p.[StartDate]

    The beauty of this is that the optimiser can use the index that you created (!) on UserCreationDate when performing the triangular join (which the optimiser doesn't really like doing anyway!)

  • 1) as others have stated that triangular join (xx <= yy) is gonna kill you performance wise as number of rows increases.

    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.

    3) SQL 2012 DOES have improved windowing function support and WILL allow for much more efficient running totals queries (among many other elegant and efficient query patterns).

    4) try something like this to get column-based output:

    select

    SUM(case when somedate >= '2011-01-01' and somedate < '2011-02-01' then amountfield else 0 end) as JanMoney,

    SUM(case when somedate >= '2011-02-01' and somedate < '2011-03-01' then amountfield else 0 end) as FebMoney,

    ...

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

  • DECLARE @dt DATE

    -- YOU HAVE THREE OPTIONS HERE, DEPENDING UPON YOUR NEED:

    --[1] SET @dt = '2010-09-23'

    --[2] SET @dt = GETDATE()

    --[3] REPLACE all @dt with GETDATE() in the below query

    -- and avoid using the above DECLARE

    SELECTCOUNT(UserId) AS 'Users YTD', DATENAME(MM, UserCreationDate) AS 'Period'

    FROMMyUser

    WHEREYEAR(UserCreationDate) = YEAR(@dt) AND UserCreationDate < DATEADD(DD, 1, @dt)

    GROUP BYDATENAME(MM, UserCreationDate), DATEPART(MM, UserCreationDate)

    ORDER BYDATEPART(MM, UserCreationDate)

    Jerry D

Viewing 15 posts - 1 through 15 (of 42 total)

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