Union, Top and Order By 1

[yeltom]

Hi, I am trying to populate a spreadsheet from SQL Server. I want to get the last Period for the 2 previous fiscal years and then the most recent 2 months of the current Fy
Example : 6/30/2003, 6/30/2004, 8/31/2004, 9/30/2004. I am trying this using a union query. I know it can be done by using static criteria but I would like to get the 2 most recent periods without having to change the SQL each month when an update is performed. Any ideas?

 

[johnherman]

Do you have a fiscal calendar table or are you willing to hard-code that the fiscal year ends June 30th each year?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[yeltom]

I do not have a Fiscal year table but I can do that if it will make it easier to accomplish the task. In my attemps to get this done I have used hard coding for the 2 previous Fy's.

 

[donutman]

Does this help?

Declare @CFY as datetime
Declare @PFY as datetime
Declare @CMO as datetime
Declare @PMO as datetime
SET @CFY=dateadd(m,((datediff(m,0,getdate())-6)/12)*12+6,0)-1
SET @PFY=dateadd(m,-12,@CFY)
SET @CMO=dateadd(m,datediff(m,0,getdate()),0)-1
SET @PMO=dateadd(m,datediff(m,0,getdate())-1,0)-1
SELECT @CFY, @PFY,@CMO,@PMO

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[johnherman]

With due respect to donutman's solution, I believe the proper thing to do from a data management perspective is to create the table. Creating the table leaves the option of changing the fiscal year and/or fiscal month, which may not mean much to you but if you acquire another company or get acquired, or just change your fiscal year, then the table solution requires very little work, just a new data record; and you still have the records of the old fiscal years.

Been there, got burned, trying to keep others from crashing and burning in similar scenarios.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[yeltom]

Thanks for the code example. It works well but I need the previous and current months to be based on data in the database. Most often the report data is not updated until the 15 days after the month has ended, say September data is not updated until Oct 15.

 

[donutman]

In place of the getdate() function in the SET statement for @CMO and @PMO just put the appropriate date from the database. Maybe I don't understand the complete problem.
John, why couldn't you solve the change in fiscal year this way:

Declare @FiscalMoEnd as int
Declare @ReportDate as datetime
Declare @CFY as datetime
Declare @PFY as datetime
Declare @CMO as datetime
Declare @PMO as datetime
SET @ReportDate='2004-12-15'
SET @FiscalMoEnd=CASE WHEN @ReportDate>'2004-01-01' THEN 9 ELSE 6 END
SET @CFY=dateadd(m,((datediff(m,0,getdate())-@FiscalMoEnd)/12)*12+@FiscalMoEnd,0)-1
SET @PFY=dateadd(m,-12,@CFY)
SET @CMO=dateadd(m,datediff(m,0,getdate()),0)-1
SET @PMO=dateadd(m,datediff(m,0,getdate())-1,0)-1
SELECT @CFY, @PFY,@CMO,@PMO

I coded it for fiscal years to end in June prior to 2004 and Sept for 2004 and after.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yeltom]

Donutman (Karl),
Thanks for the help. It is now working as expected.