Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

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

The Chadinator

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?

Go to Top of Page

Flowing Fount of Yak Knowledge

6065 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

spelling correction

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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 10/10/2012 :  23:58:36  Show Profile  Reply with Quote
here's a post on dynamic pivot

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000