Help on Creating Fiscal Year Periods

Question

Post reply

Help on Creating Fiscal Year Periods

joshtheflame

Default port

Points: 1499
More actions
August 19, 2010 at 12:31 pm

#226092

Hi,
I am developing a financial application using SQLServer 2008 as database and I want to generate fiscal periods of any year for example
Input : June 2010
Results desired
Period…………….Start…………………………………..End
1…………………1-06-2010……………………………30-06-2010
2................1-07-2010........................31-07-2010
And so on untill May 2010 …Actually need to get 12 rows (12 months) starting from any month and output will be the starting date of the month and end would be the last day of the month like for example in my scenario June 2010 - May 2011 ..what will be the query.
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

Check out this article. It should help.

http://www.sqlservercentral.com/articles/T-SQL/70482/

joshtheflame Default port Points: 1499 More actions · Answer 2

Thanks for the link but this is not my requirement i mean Calendar table and all. I just want simple query to achieve this like for example.

DECLARE @mydate DATETIME

SELECT @mydate = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,

'Last Day of Previous Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,

'First Day of Current Month' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,

'Last Day of Current Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,

'First Day of Next Month'

The above query is returning only one row with the desired result..how can i make it for 12 months by providing input of the starting month..

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

Is this what you want?

DECLARE @mydate DATETIME

SELECT @mydate = GETDATE()

DECLARE @StartDate datetime

SELECT @StartDate = DATEADD(month, DATEDIFF(month, 0, @mydate), 0)

SELECT

number AS [Period],

DATEADD(month, number - 1, @StartDate) AS [Start],

DATEADD(day, -1, DATEADD(month, number, @StartDate)) AS [End]

FROM (

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12

) N(number)

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 4

Just so I understand...

You are developing a financial application and want something to work for now, hoping it will work in the future? I have developed financial applications and find that proven patterns solve way more problems than you can even anticipate. If this is a long-term application that will do many reports or screens based on the same periods, the recommendation for the calendar table is really your best bet. It pretty much guarantess consistant results everywhere you may need that informaiton.

Just my two cents worth.

joshtheflame Default port Points: 1499 More actions · Answer 5

andrewd.smith Thanks for the answer exactly I was looking for. Hats off to ya 🙂

joshtheflame Default port Points: 1499 More actions · Answer 6

jerry-621596 (8/19/2010)
Just so I understand...
You are developing a financial application and want something to work for now, hoping it will work in the future? I have developed financial applications and find that proven patterns solve way more problems than you can even anticipate. If this is a long-term application that will do many reports or screens based on the same periods, the recommendation for the calendar table is really your best bet. It pretty much guarantess consistant results everywhere you may need that informaiton.
Just my two cents worth.

Jerry thank you for the advise actually I have short time and my financial year has 12 periods (months) which i am using to (open, close & current) to avoid wrong postings and inconsistent results. I would appreciate if you can explain the usage of calendar so I can use it later in my application.