Solved

Fiscal Month & Year Population

Posted on 2015-01-10
8
78 Views
Last Modified: 2015-01-15
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.
Can someone please help?
--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

Open in new window

0
Comment
Question by:pvsbandi
  • 3
  • 2
  • 2
8 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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

by:pvsbandi
ID: 40543050
Works!! Thank you!
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 18

Expert Comment

by:Simon
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 48

Expert Comment

by:PortletPaul
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

by:pvsbandi
ID: 40544460
I'm sorry.. i intended to accept Simon's solution.
Paul, can you please unaccept?
My apologies for the oversight.
0
 

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query for filter 12 34
Is the client attempting to connect on the correct network interface? 1 26
Mssql SQL query 14 45
Can Unique column have more than one Null? 8 48
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now