July 29, 2015 at 7:23 am
I've been thinking about this one and can not come up with a way to do this that make sense.
Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose?
Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
Thanks in advance!
July 29, 2015 at 7:38 am
So you just need the year as parameter 1, so you know which July 1st you're after. Then the number of periods, which equates to the number of months. You could use DATEADD to add the required number of months to get the start of the end month (subtracting 1 of course, because '1' would mean just July).
At this point your end date would be set for the start of whichever month, so you need to use that date to calculate the end of that month. There is an article on this site for calculating various dates, or an article by Robyn Page and Phil Factor https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/ which has a calculation for getting the last day of the month.
If you need to you can now use DATEDIFF to get the number of days between both.
July 29, 2015 at 8:04 am
kwashington (7/29/2015)
I've been thinking about this one and can not come up with a way to do this that make sense.Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose?
Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
Thanks in advance!
This code starts off with creating variables for the parameters that you would use, and assigning values to them.
Remarks in the code for what it's actually doing.
DECLARE @Year SMALLINT, @Period TINYINT;
SET @Year = 2014;
SET @Period = 2;
DECLARE @FYStart DATE = '2000-07-01';
SELECT dt.CurrentFYStart, ca1.StartOfPeriod, ca2.EndOfPeriod, ca3.DaysFromFYStartToPeriodEnd
-- get the current FY start date
FROM (SELECT DATEADD(YEAR, @Year-YEAR(@FYStart), @FYStart)) AS dt(CurrentFYStart)
-- add the number of months to get the starting date of the period
CROSS APPLY (SELECT DATEADD(MONTH, @Period-1, dt.CurrentFYStart)) ca1(StartOfPeriod)
-- jump forward 1 month, then back 1 day to get the ending date of this period
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, ca1.StartOfPeriod))) ca2(EndOfPeriod)
-- get the number of days from the start of the FY to the end of the period
CROSS APPLY (SELECT DATEDIFF(DAY, dt.CurrentFYStart, ca2.EndOfPeriod) +1) ca3(DaysFromFYStartToPeriodEnd)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 9:33 am
Thanks to both of you for taking the time to help!
It occurred to me I forgot about one major thing. The fiscal year starts in the previous calendar year. So fiscal year 2015 starts 07/01/2014.
What I get is
CurrentFYStart 2015-07-01 (should be 2014-07-01)
StartOfPeriod 2016-05-01 (should be 2015-05-01)
EndOfPeriod 2016-05-31 (should be 2015-05-31)
DaysFromFYStartToPeriodEnd 336 ( I think this should be 335?)
I tried to change the code to subtract a year but I'm obviously getting something wrong and getting syntax errors.
July 29, 2015 at 9:39 am
Amazingly, I think I may have been able to change your code to get the correct results. Is this syntax correct?
DECLARE @fiscal_year INT
SET @fiscal_year = 2015
DECLARE @fiscal_period INT
SET @fiscal_period = 11
DECLARE @Year SMALLINT, @Period TINYINT;
DECLARE @FYStart DATE = '2000-07-01';
SELECT dt.CurrentFYStart, ca1.StartOfPeriod, ca2.EndOfPeriod, ca3.DaysFromFYStartToPeriodEnd
-- get the current FY start date
FROM (SELECT DATEADD(YEAR, -1, DATEADD(YEAR, @fiscal_year-YEAR(@FYStart), @FYStart))) AS dt(CurrentFYStart)
-- add the number of months to get the starting date of the period
CROSS APPLY (SELECT DATEADD(MONTH, @fiscal_period-1, dt.CurrentFYStart)) ca1(StartOfPeriod)
-- jump forward 1 month, then back 1 day to get the ending date of this period
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, ca1.StartOfPeriod))) ca2(EndOfPeriod)
-- get the number of days from the start of the FY to the end of the period
CROSS APPLY (SELECT DATEDIFF(DAY, dt.CurrentFYStart, ca2.EndOfPeriod) +1) ca3(DaysFromFYStartToPeriodEnd)
July 29, 2015 at 11:38 am
See my select statement at the end. I use your code to set the stage.
DECLARE @fiscal_year INT
SET @fiscal_year = 2015
DECLARE @fiscal_period INT
SET @fiscal_period = 11
DECLARE @Year SMALLINT, @Period TINYINT;
DECLARE @FYStart DATE = '2000-07-01';
SELECT
dt.CurrentFYStart,
ca1.StartOfPeriod,
ca2.EndOfPeriod,
ca3.DaysFromFYStartToPeriodEnd
-- get the current FY start date
FROM
(SELECT DATEADD(YEAR, -1, DATEADD(YEAR, @fiscal_year-YEAR(@FYStart), @FYStart))) AS dt(CurrentFYStart)
-- add the number of months to get the starting date of the period
CROSS APPLY (SELECT DATEADD(MONTH, @fiscal_period-1, dt.CurrentFYStart)) ca1(StartOfPeriod)
-- jump forward 1 month, then back 1 day to get the ending date of this period
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, ca1.StartOfPeriod))) ca2(EndOfPeriod)
-- get the number of days from the start of the FY to the end of the period
CROSS APPLY (SELECT DATEDIFF(DAY, dt.CurrentFYStart, ca2.EndOfPeriod) +1) ca3(DaysFromFYStartToPeriodEnd);
-- My solution:
select
cast(dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)) as date) StartFY,
cast(dateadd(month,(@fiscal_period - 1) - 6,dateadd(year,@fiscal_year - 1900,0)) as date) StartOfPeriod,
cast(dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0))) as date) EndOfPeriod,
datediff(day,dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)),dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0)))) + 1 DaysFromFYStartToPeriodEnd;
July 29, 2015 at 11:42 am
Lynn Pettis (7/29/2015)
See my select statement at the end. I use your code to set the stage.
DECLARE @fiscal_year INT
SET @fiscal_year = 2015
DECLARE @fiscal_period INT
SET @fiscal_period = 11
DECLARE @Year SMALLINT, @Period TINYINT;
DECLARE @FYStart DATE = '2000-07-01';
SELECT
dt.CurrentFYStart,
ca1.StartOfPeriod,
ca2.EndOfPeriod,
ca3.DaysFromFYStartToPeriodEnd
-- get the current FY start date
FROM
(SELECT DATEADD(YEAR, -1, DATEADD(YEAR, @fiscal_year-YEAR(@FYStart), @FYStart))) AS dt(CurrentFYStart)
-- add the number of months to get the starting date of the period
CROSS APPLY (SELECT DATEADD(MONTH, @fiscal_period-1, dt.CurrentFYStart)) ca1(StartOfPeriod)
-- jump forward 1 month, then back 1 day to get the ending date of this period
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, ca1.StartOfPeriod))) ca2(EndOfPeriod)
-- get the number of days from the start of the FY to the end of the period
CROSS APPLY (SELECT DATEDIFF(DAY, dt.CurrentFYStart, ca2.EndOfPeriod) +1) ca3(DaysFromFYStartToPeriodEnd);
-- My solution:
select
cast(dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)) as date) StartFY,
cast(dateadd(month,(@fiscal_period - 1) - 6,dateadd(year,@fiscal_year - 1900,0)) as date) StartOfPeriod,
cast(dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0))) as date) EndOfPeriod,
datediff(day,dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)),dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0)))) + 1 DaysFromFYStartToPeriodEnd;
The following includes an alternate calculation to get number of days from start of fiscal year to end of current period:
select
cast(dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)) as date) StartFY,
cast(dateadd(month,(@fiscal_period - 1) - 6,dateadd(year,@fiscal_year - 1900,0)) as date) StartOfPeriod,
cast(dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0))) as date) EndOfPeriod,
datediff(day,dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)),dateadd(day,-1,dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0)))) + 1 DaysFromFYStartToPeriodEnd,
datediff(day,dateadd(month,-6,dateadd(year,@fiscal_year - 1900,0)),dateadd(month,(@fiscal_period) - 6,dateadd(year,@fiscal_year - 1900,0))) AltDaysFromFYStartToPeriodEnd;
July 29, 2015 at 11:57 am
Thank you so much for your help! I need to go through it bit by bit to understand how you're making it work, but it does work!
July 29, 2015 at 11:47 pm
Thought I'd throw my hat into the ring. As always, details are in the comments in the code.
CREATE FUNCTION dbo.FyParts
(@Date DATETIME)
/**************************************************************************************************
Purpose:
Given any date after 31 Dec 1899, return various obviously named date parts as a single row table
based on a fiscal year starting on 01 July of any year.
Usage:
--===== With multiple dates from a table
SELECT t.SomeDate, ca.*
FROM dbo.SomeTable t
CROSS APPLY dbo.FyParts(t.SomeDate)
;
--===== With a single date (could be a variable and can always select an individual column value)
SELECT * FROM dbo.FyParts('JAN 15, 2015')
;
Revision History:
Rev 00 - 30 Jul 2015 - Jeff Moden - Initial creation and unit test.
- Ref: http://www.sqlservercentral.com/Forums/Topic1706648-391-1.aspx
**************************************************************************************************/
RETURNS TABLE AS
RETURN WITH
cte AS (SELECT FyStart = DATEADD(mm,6,DATEADD(yy,DATEDIFF(yy,0,DATEADD(mm,-6,@Date)),0))
,MonthStart = DATEADD(mm,DATEDIFF(mm,0,@Date),0)
,MonthEnd = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,-1,@Date),0))
)
SELECT FyStart
,FyEnd = DATEADD(dd,-1,DATEADD(yy,1,FyStart))
,FyYear = DATENAME(yy,FyStart)
,FyMonth = RIGHT(DATEDIFF(mm,FyStart,@Date)+101,2)
,MonthStart
,MonthEnd
,FyDaysMonthStart = DATEDIFF(dd,FyStart,MonthStart)+1
,FyDaysMonthEnd = DATEDIFF(dd,FyStart,MonthEND) +1
,FyDaysDate = DATEDIFF(dd,FyStart,@Date) +1
FROM cte
;
You can also pass it a value like 'Jul 2015' or 'July 2015' or just about any other legal rendition of a date and it'll auto-magically work. And, yeah, if you pass it just '2015' (in quotes like that), it'll treat the data as '2015-01-01'. 'Tis the nature of the DATETIME datatype to do so. Just keep in mind that will return the values for the Jul 2014 thru Jun 2015 fiscal year because that's what the first day of 2015 is a part of.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply