Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get monthly YTD data


How to get monthly YTD data

Author
Message
kautuk
kautuk
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 198
Comments posted to this topic are about the item How to get monthly YTD data
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 615
wouldn't this actually give you all the data for a given month regardless of which year it occurred?
Kostas Kotzamanidis
Kostas Kotzamanidis
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 26
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
Junie01
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 31
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
zuber.patel
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
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
Attachments
table.bmp (0 views, 123.00 KB)
zuber.patel
zuber.patel
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
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
Attachments
table.bmp (0 views, 123.00 KB)
kautuk
kautuk
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 198
Did you try pivot() on your data? I think that will do exactly what you want...w00t
simonellistonball
simonellistonball
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 60
@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
anjali.g
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 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
kautuk
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 198
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search