Solved

Fiscal Month & Year Population

Posted on 2015-01-10
8
83 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
[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
  • Learn & ask questions
  • 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 49

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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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 49

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 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