SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Fiscal Year troubles
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jonleikvold
Starting Member

USA
1 Posts

Posted - 10/10/2012 :  14:03:46  Show Profile  Reply with Quote
My sales manager wants a report from our invoice table that would look like this

Customer FY2010 FY2011 FY2012 FY2013
ABC Inc $20,000 $18,000 $$$$$ $$$$
...
ZZZ Inc $$$$$ $$$$ $$$$$ $$$$

2 complicating factors: Our fiscal year runs from Oct 1 - Sep 30
I need to have this report run this year next year and 5 years from now without having to change the program. ie the titles must be variable.

The data comes from an invoice table with field names as follows:
customer (customer name)
document_date (the date of the invoice)
amount (the invoice amount)


I have achieved some success by adding 3 months to the document_date to get a field with the correct fiscal year, But my crosstab attempts have all failed.

Any help would be greatly appreciated.

I am running SQL server 2008 r2

Jon L

Edited by - jonleikvold on 10/10/2012 14:07:41

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/10/2012 :  15:01:47  Show Profile  Visit chadmat's Homepage  Reply with Quote
Why not use SSRS, and let the report handle the formatting?

-Chad
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/10/2012 :  16:06:48  Show Profile  Reply with Quote
if you want to do this with t-sql AND your column names will change depending on the daterange then you will need to implement a "dynamic cross tagb" or "dynamic pivot". Search this site for lots of examples.

>>But my crosstab attempts have all failed
If you need customized help then post your attempt(s)

Be One with the Optimizer
TG

EDIT:
spelling correction

Edited by - TG on 10/10/2012 16:07:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2012 :  23:58:36  Show Profile  Reply with Quote
here's a post on dynamic pivot
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000