Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harsha123_8
Starting Member
2 Posts |
 Posted - 2004-07-07 : 22:59:32
|
| I am using a crosstab query and using following statement as partof the query to get sales results.PIVOT Format(salesdate, 'yyyy')Result will beProduct 2001 2002 2003 2004ABCNow i want to get it for financial year. That is from 1st April previous year to 31st March current year. (Eg. 1st April 2001-31st March 2002, 1st April 2002 - 31st March 2003 etc..)like thisProduct 2001/2002 2002/2003 2003/2004ABCCan any genius help please. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-07-08 : 04:01:04
|
| i think you'll need some sort of mapping table to define the relationship between calendar dates and financial periods....ie....01/May /2001 is in Q1-FYR-01-02 and thus also in FYR-01-0207/Aug /2001 is in Q2-FYR-01-02 and thus also in FYR-01-0215/Sept/2001 is in Q2-FYR-01-02 and thus also in FYR-01-0231/Dec /2001 is in Q3-FYR-01-02 and thus also in FYR-01-0208/Feb /2002 is in Q4-FYR-01-02 and thus also in FYR-01-0211/May /2002 is in Q1-FYR-02-03 and thus also in FYR-02-0317/Aug /2002 is in Q2-FYR-02-03 and thus also in FYR-02-0325/Sept/2003 is in Q2-FYR-03-04 and thus also in FYR-03-0431/Dec /2003 is in Q3-FYR-03-04 and thus also in FYR-03-0408/Feb /2004 is in Q4-FYR-03-04 and thus also in FYR-03-04The middle step is currently overkill for your stated requirements....but if your users are anything like my crowd...then as soon as you give them full year figures...you'll also need to give them quarter-on-quarter stuff as well!!! |
 |
|
|
|
|
|
Using PIVOT in Access