Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help on Creating Fiscal Year Periods Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 12:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
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
Post #972120
Posted Thursday, August 19, 2010 12:37 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:58 PM
Points: 367, Visits: 614
Check out this article. It should help.

http://www.sqlservercentral.com/articles/T-SQL/70482/
Post #972122
Posted Thursday, August 19, 2010 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
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..

Post #972130
Posted Thursday, August 19, 2010 2:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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)

Post #972162
Posted Thursday, August 19, 2010 4:01 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:58 PM
Points: 367, Visits: 614
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.
Post #972221
Posted Friday, August 20, 2010 6:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
andrewd.smith Thanks for the answer exactly I was looking for. Hats off to ya :)
Post #972459
Posted Friday, August 20, 2010 6:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
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.

Post #972465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse