November 17, 2015 at 10:59 pm
Comments posted to this topic are about the item A Single-Parameter Date Range in SQL Server Reporting Services
November 18, 2015 at 12:22 am
Hi. Thanks for that. it is very help full. But, Mostly in Financial reporting, it is required to present the reports by Month1, Month2, Month3....Month60 as separate columns of data so we can compare data on a monthly basis.
November 18, 2015 at 2:37 am
Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.
However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.
November 18, 2015 at 4:06 am
eavesdm (11/18/2015)
Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.
Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:
select Period = convert(varchar(100), 'Year To Date')
,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)
,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)
union all
select Period = convert(varchar(100), 'Next Year')
,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))
,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))
Good article and good idea though.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2015 at 4:51 am
Excellent article - this technique is invaluable, especially for scheduling reports with varying periods.
Uncannily, after a long time thinking "there must be a better way to choose dates", I sat down this very week and wrote pretty much the same thing!
My approach is slightly different. I didn't want to join a DateRange table to all the data sources so I make the DateRange selector "populate" (via auto refresh) two normal but hidden StartDate and EndDate parameters. The StartDate and EndDate parameters are consumed by the data source as normal.
I can also leave the StartDate and EndDate parameters visible to allow users still to override the standard DateRanges with ad-hoc periods.
My implementation is as a stored procedure which can be called once to populate the DateRange drop-down list, and again to obtain the selected dates to set the default values of the StartDate and EndDate parameters.
I'm expecting to expand the list of ranges...
CREATE PROCEDURE [parm].[DateRanges]
/*
Use to populate SSRS date ranges to provide a set of standard relative date ranges
(e.g. Last Day, Last Month).
PARAMETER
@DateRangeId : set to 0 to return all date ranges. Use this mode for populating a
dropdown list parameter named "DateRangeId", Label "Date Range".
*/
@DateRangeId int = 0
AS
SET NOCOUNT ON;
DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @LastMonday date = DATEADD(WEEK, DATEDIFF(WEEK, '19060101', GETDATE()), '19060101');
DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) ;
DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1), @LastDayOfPrecedingMonth) AS date) ;
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday) AS date);
WITH CTE AS (
SELECT * FROM
( VALUES
( 1, 'Today', CAST(GETDATE() AS date), CAST(GETDATE() AS date) ),
( 2, 'Yesterday', @Yesterday, @Yesterday ),
( 3, '2 Days Ago', DATEADD(dd, -1, @Yesterday), DATEADD(dd, -1, @Yesterday) ),
(101, 'Last 7 Days', DATEADD(dd, -6, @Yesterday), @Yesterday ),
(102, 'Week to Yesterday', DATEADD(DAY, -6, @Yesterday), @Yesterday ),
(201, 'Last 10 Days', DATEADD(dd, -9, @Yesterday), @Yesterday),
(301, 'Calendar Month to Yesterday', @FirstDayOfYesterdaysMonth, @Yesterday),
(302, 'Two Months to Yesterday', DATEADD(MM, -1, @FirstDayOfYesterdaysMonth), @Yesterday),
(303, 'Three Months to Yesterday', DATEADD(MM, -2, @FirstDayOfYesterdaysMonth), @Yesterday),
(306, 'Six Months to Yesterday', DATEADD(MM, -5, @FirstDayOfYesterdaysMonth), @Yesterday)
) DT (DateRangeId, DisplayName, StartDate, EndDate)
)
SELECT
CTE.DateRangeId AS DateRangeKey,
CTE.DisplayName AS DateRangeLabel,
CTE.StartDate,
CTE.EndDate
FROM
CTE
WHERE
(@DateRangeId = 0)
OR
(@DateRangeId = CTE.DateRangeId)
November 18, 2015 at 6:31 am
Great article. Couple of typos in your code that do not affect the execution. Look for "EndDateunion" aliases.
Thanks for the article.
[Edit]
BTW, we have a commercial product that does something similar and allows the following options you might consider coding:
Today, Yesterday, Tomorrow,
Current week, month, quarter, semester, year
Last # days, weeks, months, quarters, semesters, years
Last week, month, quarter, semester, year
Next # days, weeks, months, quarters, semesters, years
# days ago
November 18, 2015 at 8:11 am
eavesdm (11/18/2015)
Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.
Thanks. I will look into this and perhaps issue a follow-up to the article.
November 18, 2015 at 8:13 am
Desh Maharaj (11/18/2015)
Hi. Thanks for that. it is very help full. But, Mostly in Financial reporting, it is required to present the reports by Month1, Month2, Month3....Month60 as separate columns of data so we can compare data on a monthly basis.
Hi - This seems to be a separate issue. This would help you to obtain the range of dates for which you need to retrieve rows. To separate monthly totals to columns, I would use a PIVOT.
November 18, 2015 at 8:16 am
Phil Parkin (11/18/2015)
eavesdm (11/18/2015)
Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.
Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:
select Period = convert(varchar(100), 'Year To Date')
,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)
,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)
union all
select Period = convert(varchar(100), 'Next Year')
,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))
,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))
Good article and good idea though.
Hi - Thanks for the advice; I'd not seen great expense in casting the year to varchar versus a dateadd on top of a datediff.
Thanks
John.
November 18, 2015 at 8:29 am
Great idea, thanks for taking time to share your work.
November 18, 2015 at 8:39 am
SQLBlimp (11/18/2015)
Phil Parkin (11/18/2015)
eavesdm (11/18/2015)
Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.
Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:
select Period = convert(varchar(100), 'Year To Date')
,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)
,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)
union all
select Period = convert(varchar(100), 'Next Year')
,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))
,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))
Good article and good idea though.
Hi - Thanks for the advice; I'd not seen great expense in casting the year to varchar versus a dateadd on top of a datediff.
Thanks
John.
And in this case, you are right! I should have checked execution plans etc before posting. My apologies.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2015 at 8:41 am
joeroshan (11/18/2015)
Great idea, thanks for taking time to share your work.
+1
November 18, 2015 at 8:48 am
Great article, it's like you read my mind, I just started back working with SSRS and was just planning on doing something exactly like this. Thanks!
Jacque
November 18, 2015 at 8:48 am
Thanks for this great tip!
One small change that I would make to avoid the "DR.EndDate+1" part and still be able to easily link to dates in other table is to create a new column adding end of day (i.e. 23:59:59.997) to the EndDate of period that have already ended and GETDATE() for current periods (e.g. last month has ended, but month to date is current). This way you can have something like
SELECT b.Period, sales = SUM(a.trn_amt)
FROM T_TRANSACTION a
JOIN V_DATE_RANGE b ON a.trn_dt BETWEEN b.BeginDate AND b.EODEndDate
GROUP BY b.Period
FM
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply