Solved

Fiscal Month & Year Population

Posted on 2015-01-10
8
77 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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Works!! Thank you!
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
Tanks much, again! and sorry for the confusion.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

12 Experts available now in Live!

Get 1:1 Help Now