How to get monthly YTD data

Question

Post reply

How to get monthly YTD data

kautuk

SSC-Addicted

Points: 438
More actions
January 23, 2012 at 10:23 pm

#391382

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

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

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

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 1

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

Kostas Kotzamanidis Grasshopper Points: 17 More actions · Answer 2

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 Right there with Babe Points: 744 More actions · Answer 3

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 More actions · Answer 4

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

zuber.patel SSC Rookie Points: 28 More actions · Answer 5

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

kautuk SSC-Addicted Points: 438 More actions · Answer 6

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

simonellistonball SSC Veteran Points: 271 More actions · Answer 7

@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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

anjali.g

Grasshopper

Points: 19

January 24, 2012 at 5:04 am

#1437584

Thanks Rookie...

acardullo Grasshopper Points: 14 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 SSC-Addicted Points: 454 More actions · Answer 14

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