May 7, 2012 at 3:55 pm
Hello All,
I need help with a function.
I give my report users the options below so that my function can generate start and end dates for them.
MostRecentYearToDate
MostRecentCompletedYear
MostRecentThreeMonthPeriod
MostRecentCompleteQuarter
Since I am not doing any holiday or workday logic I am hoping to do this without a calendar table.
Note: Due to the nature of my source data the start date is is always the last date of the previous month. So MostRecentYearToDate is 12/31/2011-4/30/2012 not 1/1/2012-4/30/2012. My source data actually has start and end date as well, there is no 'between logic' here.
The definition of MostRecentCompleteQuarter is the standard quarters ending in March, June, Sept or December.
MostRecentThreeMonthPeriod is returning an incorrect start date. The start date should be 1/31/2012 not 1/30/2012.
Can anyone see a clean way to fix this?
Is my method inherently unreliable since I am not using a calendar table?
Thanks to anyone who could help.
CREATE FUNCTION [dbo].[GetTimePeriod]
(
@Period VARCHAR(50)
)
RETURNS TABLE AS
RETURN
SELECT Start_Date = DATEADD(day, -1, DATEADD(year, DATEDIFF(year, '',GETDATE()),'')),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentYearToDate'
UNION ALL
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentCompletedYear'
UNION ALL
SELECT Start_Date = DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentThreeMonthPeriod'
UNION ALL
SELECT Start_Date = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(mm,-3,GETDATE())),0)),
End_Date = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1)
WHERE @Period = 'MostRecentCompleteQuarter'
SELECT * FROM dbo.GetTimePeriod('MostRecentYearToDate')
SELECT * FROM dbo.GetTimePeriod('MostRecentCompletedYear')
SELECT * FROM dbo.GetTimePeriod('MostRecentThreeMonthPeriod')
SELECT * FROM dbo.GetTimePeriod('MostRecentThreeMonthPeriod')
May 7, 2012 at 4:00 pm
can you post the output you want from the 4 queries. when i run them i get
Start_Date End_Date
----------------------- -----------------------
2011-12-31 00:00:00.000 2012-04-30 00:00:00.000
(1 row(s) affected)
Start_Date End_Date
----------------------- -----------------------
2011-04-30 00:00:00.000 2012-04-30 00:00:00.000
(1 row(s) affected)
Start_Date End_Date
----------------------- -----------------------
2012-01-30 00:00:00.000 2012-04-30 00:00:00.000
(1 row(s) affected)
Start_Date End_Date
----------------------- -----------------------
2012-01-30 00:00:00.000 2012-04-30 00:00:00.000
(1 row(s) affected)
and from your explanations im having a hard time understanding what you are asking for.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 7, 2012 at 4:00 pm
Using todays date (20120507) what would be the return values for each call to your function?
May 7, 2012 at 4:07 pm
Period,Start_Date,End_Date
MostRecentYearToDate,2011-12-31,2012-04-30
MostRecentCompletedYear,2011-04-30,2012-04-30
MostRecentThreeMonthPeriod,2012-01-31,2012-04-30
MostRecentCompleteQuarter,2011-12-31,2012-03-31
Both start and end date should always be the last day of the month.
The function as I posted it 'seems' to be working except for the start date for MostRecentThreeMonthPeriod.
May 7, 2012 at 4:17 pm
Use this for your third calculation:
SELECT Start_Date = DATEADD(dd, -1,DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))),
End_Date = DATEADD(dd, -1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))
May 7, 2012 at 4:29 pm
Thanks! Looks good from the testing I have done.
DECLARE @Period VARCHAR(50)
DECLARE @TestDate datetime
--SET @Period = 'MostRecentYearToDate'
SET @Period = 'MostRecentCompletedYear'
--SET @Period = 'MostRecentThreeMonthPeriod'
--SET @Period = 'MostRecentCompleteQuarter'
--SET @TestDate = '12/5/2011'
--SET @TestDate = '2/29/2012'
SET @TestDate = '3/31/2012'
SELECT Start_Date = DATEADD(day, -1, DATEADD(year, DATEDIFF(year, '',@TestDate),'')),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,@TestDate),0)-1
WHERE @Period = 'MostRecentYearToDate'
UNION ALL
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@TestDate),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,@TestDate),0)-1
WHERE @Period = 'MostRecentCompletedYear'
UNION ALL
SELECT Start_Date = DATEADD(dd, -1,DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,@TestDate),0))),
End_Date = DATEADD(dd, -1,DATEADD(MONTH,DATEDIFF(MONTH,0,@TestDate),0))
WHERE @Period = 'MostRecentThreeMonthPeriod'
UNION ALL
SELECT Start_Date = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(mm,-3,@TestDate)),0)),
End_Date = DATEADD(qq,DATEDIFF(qq,0,@TestDate),-1)
WHERE @Period = 'MostRecentCompleteQuarter'
GO
May 7, 2012 at 4:43 pm
It seems IF ELSE can't be used in a function hence the UNION ALL technique. Any way to not call the function 4 times? Or is the performance implication so minimal that I should move on to more important matters.
This doesn't work.
CREATE FUNCTION [dbo].[TestGetTimePeriod]
(
@Period VARCHAR(50)
)
RETURNS TABLE AS
RETURN
IF @Period = 'MostRecentYearToDate'
BEGIN
SELECT Start_Date = DATEADD(day, -1, DATEADD(year, DATEDIFF(year, '',GETDATE()),'')),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentYearToDate'
END
IF @Period = 'MostRecentCompletedYear'
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentCompletedYear'
END
IF @Period = 'MostRecentThreeMonthPeriod'
SELECT Start_Date = DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentThreeMonthPeriod'
END
IF @Period = 'MostRecentCompleteQuarter'
BEGIN
SELECT Start_Date = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(mm,-3,GETDATE())),0)),
End_Date = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1)
WHERE @Period = 'MostRecentCompleteQuarter'
END
CREATE FUNCTION [dbo].[TestGetTimePeriod]
(
@Period VARCHAR(50)
)
RETURNS TABLE AS
RETURN
...nor this...
SELECT
Start_Date,
End_Date
FROM
(
CASE @Period
WHEN 'MostRecentYearToDate' THEN
SELECT Start_Date = DATEADD(day, -1, DATEADD(year, DATEDIFF(year, '',GETDATE()),'')),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentYearToDate'
END
WHEN 'MostRecentCompletedYear' THEN
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentCompletedYear'
END
WHEN 'MostRecentThreeMonthPeriod' THEN
SELECT Start_Date = DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentThreeMonthPeriod'
END
WHEN 'MostRecentCompleteQuarter' THEN
SELECT Start_Date = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(mm,-3,GETDATE())),0)),
End_Date = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1)
WHERE @Period = 'MostRecentCompleteQuarter'
END
) x
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply