Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Fiscal Month & Year Population

Posted on 2015-01-10
Medium Priority
85 Views
Greetings,

We have a table that gives us the current fiscal year. Based on this year, we'll have to come up with a fiscal month &year table that stores the months and years of the current and previous fiscal years and other information, as displayed below.
``````--Existing CURR_Fiscal_Year Table
Year      Fiscal_yr_Start_Dt	                 Fiscal_yr_End_Dt
2015		10/01/2014				09/30/2015

-- New Fiscal Year Table
Month	Year	Fiscal_yr_Start_Dt	                     Fiscal_Year_End_DT
10		2014	10/01/2013				09/30/2014
11		2014	10/01/2013				09/30/2014
12		2014	10/01/2013				09/30/2014
01		2014	10/01/2013				09/30/2014
02		2014	10/01/2013				09/30/2014
03		2014	10/01/2013				09/30/2014
04		2014	10/01/2013				09/30/2014
05		2014	10/01/2013				09/30/2014
06		2014	10/01/2013				09/30/2014
07		2014	10/01/2013				09/30/2014
08		2014	10/01/2013				09/30/2014
09		2014	10/01/2013				09/30/2014
10		2015	10/01/2014				09/30/2015
11		2015	10/01/2014				09/30/2015
12		2015	10/01/2014				09/30/2015
01		2015	10/01/2014				09/30/2015
02		2015	10/01/2014				09/30/2015
03		2015	10/01/2014				09/30/2015
04		2015	10/01/2014				09/30/2015
05		2015	10/01/2014				09/30/2015
06		2015	10/01/2014				09/30/2015
07		2015	10/01/2014				09/30/2015
08		2015	10/01/2014				09/30/2015
09		2015	10/01/2014				09/30/2015
``````
0
Question by:pvsbandi
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 18

Accepted Solution

Simon earned 2000 total points
ID: 40542547
Here's one way to do it...
``````DECLARE @FinYears AS TABLE (FinYear INT)
-- insert current and all the previous years you require into the table
insert into @FinYears (FinYear)
SELECT 2015 UNION ALL
SELECT 2014 UNION ALL
SELECT 2013

DECLARE @Months AS TABLE (MonthNumString CHAR(2))
INSERT INTO @Months  (MonthNumString)
VALUES ('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')

SELECT
MonthNumString AS [Month]
,FinYear as [Year]
,CAST(CONVERT(CHAR(4),FinYear-1)+'1001' AS DATE) AS Fiscal_yr_Start_Dt
,CAST(CONVERT(CHAR(4),FinYear)+'0930' AS DATE) AS Fiscal_yr_End_Dt
FROM @FinYears CROSS JOIN @Months
ORDER BY FinYear,MonthNumString
``````
0

LVL 49

Expert Comment

ID: 40542717
When we enter a date without any time reference the time is assumed to be 00:00:00 +00000 (start of the day)
So, 09/30/2015 is at the START of the day, and the 24 hour duration of that day is ignored.

i.e. For a fiscal year commencing 10/01/2014, the end date is actually 10/01/2015
i.e. where all data >= 10/01/2014 and < 10/01/2015 are included in that fiscal year.

Why would you store the fiscal year start/end against every fiscal month? and not store the fiscal month start/end dates?
0

Author Comment

ID: 40543050
Works!! Thank you!
0

LVL 18

Expert Comment

ID: 40543214
@pvsbandi - What works? I posted the only code in this thread but you've marked PortletPaul's comment as the solution? While I'd agree that he raised some valid points (to take care if comparing datetimes against your date ranges and whether it would be more useful to store period start/end dates rather than year start/end dates in the table), he hasn't provided a solution.
It's your question, so it is up to you whose solution you accept, but I'd appreciate it if you reviewed your choice on this question.
0

LVL 49

Expert Comment

ID: 40543469
I agree with Simon. I was asking questions (which I hope you don't ignore.)

I can "unaccept" the question for you if want, just ask here. Or, use the "Request Attention" link under the question.
0

Author Comment

ID: 40544460
I'm sorry.. i intended to accept Simon's solution.
My apologies for the oversight.
0

Author Closing Comment

ID: 40551024
Tanks much, again! and sorry for the confusion.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll