Solved

Fiscal Month & Year Population

Posted on 2015-01-10
8
79 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rename SQL Instance/SQL Developer Edition 2012 2 19
SQL Pivot Rows To Columns 10 52
SQL Server tables join on parse list 6 22
This query failed in sql 2014 5 29
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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

806 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