How to get monthly YTD data

  • kautuk

    SSC-Addicted

    Points: 438

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

  • lnardozi 61862

    SSCommitted

    Points: 1910

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

  • Kostas Kotzamanidis

    Grasshopper

    Points: 17

    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

  • Junie01

    Say Hey Kid

    Points: 664

    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

  • zuber.patel

    SSC Rookie

    Points: 28

    Hi Rookie,

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

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

    UserYTD 6 25 33 39 50 55 64 161 274 281

  • zuber.patel

    SSC Rookie

    Points: 28

    Hi Rookie,

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

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

    UserYTD 6 25 33 39 50 55 64 161 274 281

    Regards,

    Zuber

  • kautuk

    SSC-Addicted

    Points: 438

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

  • simonellistonball

    SSC Veteran

    Points: 271

    @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.

  • anjali.g

    Grasshopper

    Points: 19

    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 .

  • kautuk

    SSC-Addicted

    Points: 438

    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

  • anjali.g

    Grasshopper

    Points: 19

    Thanks Rookie...

  • acardullo

    Grasshopper

    Points: 14

    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?

  • pierre-702284

    SSC Veteran

    Points: 216

    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!)

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • Jerry Day

    Old Hand

    Points: 374

    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

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

    FROM MyUser

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

    GROUP BY DATENAME(MM, UserCreationDate), DATEPART(MM, UserCreationDate)

    ORDER BY DATEPART(MM, UserCreationDate)

    Jerry D

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

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