A Single-Parameter Date Range in SQL Server Reporting Services

  • Lynn Pettis

    SSC Guru

    Points: 442360

    My fiscal year calculation is wrong.  Give me a couple of minutes to fix it.

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Here is the updated view:


    create view [V_DATE_RANGE] as
    select
     convert(varchar(100), 'Year To Date') as [Period]
     , dateadd(year, datediff(year, 0, getdate()), 0) BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Today') as [Period]
     , convert(datetime, convert(date, getdate())) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Yesterday') as [Period]
     , convert(datetime, convert(date, dateadd(day, -1, getdate()))) as BeginDate
     , convert(datetime, convert(date, dateadd(day, -1, getdate()))) as EndDate
    union all
    select
     convert(varchar(100), 'Fiscal Year To Date') as [Period]
     , dateadd(month, -3, dateadd(year,datediff(year, 0, dateadd(month, 3, getdate())), 0)) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Month To Date') as [Period]
     , dateadd(month, datediff(month, 0, getdate()), 0) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last Month') as [Period]
     , dateadd(month, datediff(month, 0, getdate()) - 1, 0) as BeginDate
     , dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0)) as EndDate
    union all
    select
     convert(varchar(100), 'Last Month to Date') as [Period]
     , dateadd(month, datediff(month, 0, getdate()) - 1, 0) as BeginDate
     , dateadd(month, -1, convert(datetime, convert(date, getdate()))) as EndDate
    union all
    select
     convert(varchar(100), 'Last Year') as [Period]
     , dateadd(year, datediff(year, 0, getdate()) - 1, 0) as BeginDate
      , dateadd(year, datediff(year, 0, getdate()), -1) as EndDate
    union all
    select
     convert(varchar(100), 'Last Year to Date') as [Period]
     , dateadd(year, datediff(year, 0, getdate()) - 1, 0) as BeginDate
     , dateadd(year, -1, convert(datetime, convert(date, getdate()))) as EndDate
    union all
    select
     convert(varchar(100), 'Last 7 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -7, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 14 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -14, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 21 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -21, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 28 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -28, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 30 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -30, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 60 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -60, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last 90 days') as [Period]
     , convert(datetime, convert(date, dateadd(day, -90, getdate()))) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'This Calendar Quarter') as [Period]
     , dateadd(quarter, datediff(quarter,0, getdate()),0) as BeginDate
     , dateadd(quarter, datediff(quarter,0, getdate()) + 1, -1) as EndDate
    union all
    select
     convert(varchar(100), 'This Calendar Quarter to Date') as [Period]
     , dateadd(quarter, datediff(quarter, 0, getdate()), 0) as BeginDate
     , convert(datetime, convert(date, getdate())) as EndDate
    union all
    select
     convert(varchar(100), 'Last Calendar Quarter') as [Period]
     , dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0) BeginDate
     , dateadd(quarter, datediff(quarter, 0, getdate()), -1) EndDate
    union all
    select
     convert(varchar(100), 'Last Calendar Quarter to Date') as [Period]
     , dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0) BeginDate
     , dateadd(quarter, -1, convert(datetime, convert(date, getdate()))) as EndDate
    union all
    select
     convert(varchar(100), 'Last Year Calendar Quarter') as [Period]
     , dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()), 0)) as BeginDate
     , dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()) + 1, -1)) as EndDateunion
    union all
    select
     convert(varchar(100), 'Last Year Calendar Quarter to Date') as [Period]
     , dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()), 0)) as BeginDate
     , dateadd(year, -1, convert(datetime, convert(date, getdate()))) as EndDate
    union all
    select
     convert(varchar(100), 'Next Year') as [Period]
     , dateadd(year, datediff(year, 0, getdate()) + 1, 0) BeginDate
     , dateadd(year, datediff(year, 0, getdate()) + 2, -1) EndDate
    union all
    select
     convert(varchar(100), 'Next Year to Date') as [Period]
     , dateadd(year, datediff(year, 0, getdate()) + 1, 0) BeginDate
     , dateadd(year, 1, cast(cast(getdate() as date) as datetime)) EndDate
    GO

  • Jorge Manzur

    Valued Member

    Points: 69

    I think there is a minor bug in "Last Month To Date" option, in EndDate calculation.
    Also in "Last Calendar Quarter to Date" option.

    Both must be calculated as same as other "... To Date" options.
    Thanks for share!

  • chad 62627

    Old Hand

    Points: 330

    Thought I would throw together my own version of this, one that could be read a little easier. I removed a few of the items from the view that I will never use (next year, fiscal year, etc). Not saying my way is better than any other way, just want to share another way of doing it.

    I built it this way so that it can be put either into a view (replace @SeedDate with GETDATE() or whatever) or into a function and you can control what the "Seed Date" is, it's not necessarily based on GETDATE(). I write a lot of reports where they will want to run a report "As of" a particular date, so having this as a function would help quite a bit. For example, run the report "As of" 3/15/2017, but they select "Last XX days" or one of the other options.

    I also changed the "Last XX Days" to be one day shorter in the range. Since the range is inclusive, I don't like to include that extra day. If today is 4/10/2017 (day 1), then a 7 day range for reporting would be 4/4/2017. I like to use the BETWEEN clause which is inclusive in T-SQL.

    And one last thing that I added on was the column EndOfDayDate, which is the end of they day to the last second. Seems crazy, but I like using it because it makes querying tables with DateTime columns easier and avoiding a DATE conversion.

    This code currently has the SeedDate set to - 1 day as pretty much all of the reports that I have written are to be run "As of" the last complete day, so whether this is run at 3AM or 11PM it's always going to be as of yesterday end of day.

    Feel free to tear apart:

    DECLARE @SeedDate DATETIME = GETDATE()

    SELECT [Period]        = CONVERT(VARCHAR(100), x.[Period])
        , BeginDate        = x.BeginDate
        , EndDate        = x.EndDate
        , EndOfDayDate    = x.EndDate + CONVERT(DATETIME, '23:59:59')
    FROM (
        SELECT Today = CONVERT(DATETIME, CONVERT(DATE, @SeedDate)) - 1 --Reporting on last complete day
    ) t
        CROSS APPLY (SELECT DiffYY    = DATEDIFF(yy, 0, t.Today)
                        , DiffMM    = DATEDIFF(mm, 0, t.Today)
                        , DiffDD    = DATEDIFF(dd, 0, t.Today)
                        , DiffQQ    = DATEDIFF(qq, 0, t.Today)
        ) y
        CROSS APPLY (
            VALUES  ('Year To Date'                            , DATEADD(yy, y.DiffYY, 0)                    , t.Today                                            )
                    , ('Today'                                    , t.Today                                    , t.Today                                            )
                    , ('Yesterday'                                , t.Today - 1                                , t.Today - 1                                        )
                    , ('Month To Date'                            , DATEADD(mm, y.DiffMM, 0)                    , t.Today                                            )
                    , ('Previous Month'                            , DATEADD(mm, y.DiffMM - 1, 0)                , DATEADD(mm, y.DiffMM, 0) - 1                        )
                    , ('Previous Month to Date'                    , DATEADD(mm, y.DiffMM - 1, 0)                , DATEADD(mm, -1, t.Today)                            )
                    , ('Previous Year'                            , DATEADD(yy, y.DiffYY - 1, 0)                , DATEADD(yy, y.DiffYY, 0) - 1                        )
                    , ('Previous Year to Date'                    , DATEADD(yy, y.DiffYY - 1, 0)                , DATEADD(yy, -1, t.Today)                            )
                    , ('Last 7 days'                            , t.Today - ( 7 - 1)                        , t.Today                                            )
                    , ('Last 14 days'                            , t.Today - (14 - 1)                        , t.Today                                            )
                    , ('Last 21 days'                            , t.Today - (21 - 1)                        , t.Today                                            )
                    , ('Last 28 days'                            , t.Today - (28 - 1)                        , t.Today                                            )
                    , ('Last 30 days'                            , t.Today - (30 - 1)                        , t.Today                                            )
                    , ('Last 60 days'                            , t.Today - (60 - 1)                        , t.Today                                            )
                    , ('Last 90 days'                            , t.Today - (90 - 1)                        , t.Today                                            )
                    , ('Calendar Quarter'                        , DATEADD(qq, y.DiffQQ, 0)                    , DATEADD(qq, y.DiffQQ + 1, 0) - 1                    )
                    , ('Calendar Quarter to Date'                , DATEADD(qq, y.DiffQQ, 0)                    , t.Today                                            )
                    , ('Previous Calendar Quarter'                , DATEADD(qq, y.DiffQQ - 1, 0)                , DATEADD(qq, y.DiffQQ, 0) - 1                        )
                    , ('Previous Calendar Quarter to Date'        , DATEADD(qq, y.DiffQQ - 1, 0)                , DATEADD(qq, -1, t.Today)                            )
                    , ('Previous Year Calendar Quarter'            , DATEADD(yy, -1, DATEADD(qq, y.DiffQQ, 0))    , DATEADD(yy, -1, DATEADD(qq, y.DiffQQ + 1, 0) - 1)    )
                    , ('Previous Year Calendar Quarter to Date'    , DATEADD(yy, -1, DATEADD(qq, y.DiffQQ, 0))    , DATEADD(yy, -1, t.Today)                            )
        ) x([Period], BeginDate, EndDate)

  • Stan Zos

    Valued Member

    Points: 59

    Great post John and chad 62627, and it's great to see how people are bringing suggestions to improve it. Well done 🙂

    chad 62627, I have a bit of problem joining it to the main dataset. Can you please help me?  How would you join it to populate the drop-down list in SSRS ?

    I would like to join with  DATEADD(YEAR,9,OrderDate) column.

    Here is my code:

    WITH Orders AS
    (

        SELECT [SalesOrderID]
             ,[OrderDate]
             ,[DueDate]
             ,[ShipDate]
             ,[SalesOrderNumber]
             ,[PurchaseOrderNumber]
             ,sc.[CustomerID]
             ,[SalesPersonID]
             ,soh.[TerritoryID]
             ,st.Name AS Territory
             ,st.[Group] AS Region
             ,sc.AccountNumber
             ,[SubTotal]
             ,[TaxAmt]
             ,[Freight]
             ,[TotalDue]
         FROM [Sales].[SalesOrderHeader] soh
         INNER JOIN Sales.Customer AS sc ON soh.CustomerID = sc.CustomerID
         INNER JOIN Sales.SalesTerritory AS st ON soh.TerritoryID = st.TerritoryID
    )
    SELECT    SalesOrderID
             ,SalesOrderNumber
             ,AccountNumber
             ,Territory
             ,Region
             ,DATEADD(YEAR,9,OrderDate) AS OrderDate
             ,DATEADD(YEAR,9,DueDate) AS DuerDate
             ,DATEADD(YEAR,9,ShipDate) AS ShipDate
             ,SubTotal
             ,TaxAmt
             ,Freight
             ,TotalDue
             ,DR.Period
    FROM Orders

    Thanks,
    S.

  • chad 62627

    Old Hand

    Points: 330

    Stan Zos - Thursday, May 11, 2017 10:53 PM

    Great post John and chad 62627, and it's great to see how people are bringing suggestions to improve it. Well done 🙂

    chad 62627, I have a bit of problem joining it to the main dataset. Can you please help me?  How would you join it to populate the drop-down list in SSRS ?

    I would like to join with  DATEADD(YEAR,9,OrderDate) column.

    Hey Stan, here's how I'm implementing the code (below). I've placed it into a function and it's still a work in progress as I'm working on implementing a datetime offset into it. So ignore that parameter for now, but this would be used like this:

    Leaving the "DateCode" parameter NULL would return every row possible. But, you can also provide a DateCode to only return a single row. I found it easiest to do it this way and I seem to remember the codes pretty easily. D M Q Y for Day Month Quarter Year, if it starts with P that means Previous, if it ends with TD then that means "To date". LXXD means Last XX days.

    SELECT * FROM dbo.uf_DateCalc(NULL, NULL, NULL)

    So you can use the NULL DateCode option to populate the report drop down. And if it were me, you would just pass the Begin and End Dates into your Start and From parameters on your report.

    I've also found that this function is useful when writing procs and makes it easier than trying to remember the snippet of code that gives you beginning of month, beginning of year, etc.


    CREATE FUNCTION dbo.uf_DateCalc (    
        @SeedDate DATE,
        @DateCode VARCHAR(5),
        @OffSet INT
    )
    RETURNS @Return TABLE (
        Code            VARCHAR(5),
        Label            VARCHAR(100),
        BeginDate        DATETIME,
        EndDate            DATETIME,
        EndOfDayDate    DATETIME
    )
    AS
    BEGIN
        SELECT @OffSet        = COALESCE(@OffSet, 0)
            , @SeedDate    = COALESCE(@SeedDate, GETDATE())

        INSERT INTO @Return (Code, Label, BeginDate, EndDate, EndOfDayDate)
        SELECT Code            = x.Code
            , Label            = CONVERT(VARCHAR(100), x.[Period])
            , BeginDate        = CONVERT(DATETIME, x.BeginDate)
            , EndDate        = CONVERT(DATETIME, x.EndDate)
            , EndOfDayDate    = x.EndDate + CONVERT(DATETIME, '23:59:59.997')
        FROM (
            SELECT Today = CONVERT(DATETIME, @SeedDate) --Reporting on last complete day
        ) t
            CROSS APPLY (SELECT DiffYY    = DATEDIFF(yy, 0, t.Today)
                            , DiffMM    = DATEDIFF(mm, 0, t.Today)
                            , DiffDD    = DATEDIFF(dd, 0, t.Today)
                            , DiffQQ    = DATEDIFF(qq, 0, t.Today)
            ) y
            CROSS APPLY (
                VALUES      ('D'        , 'Day'                                , t.Today                        , t.Today                            )
                        , ('M'        , 'Month'                            , DATEADD(mm, y.DiffMM, 0)        , DATEADD(mm, y.DiffMM + 1, 0) - 1    )
                        , ('MTD'    , 'Month To Date'                    , DATEADD(mm, y.DiffMM, 0)        , t.Today                            )
                        , ('Q'        , 'Quarter'                            , DATEADD(qq, y.DiffQQ, 0)        , DATEADD(qq, y.DiffQQ + 1, 0) - 1    )
                        , ('QTD'    , 'Quarter to Date'                    , DATEADD(qq, y.DiffQQ, 0)        , t.Today                            )
                        , ('YTD'    , 'Year To Date'                    , DATEADD(yy, y.DiffYY, 0)        , t.Today                            )

                        , ('PD'        , 'Previous Day'                    , t.Today - 1                    , t.Today - 1                        )
                        , ('PM'        , 'Previous Month'                    , DATEADD(mm, y.DiffMM - 1, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
                        , ('PMTD'    , 'Previous Month to Date'            , DATEADD(mm, y.DiffMM - 1, 0)    , DATEADD(mm, -1, t.Today)            )
                        , ('PQ'        , 'Previous Quarter'                , DATEADD(qq, y.DiffQQ - 1, 0)    , DATEADD(qq, y.DiffQQ, 0) - 1        )
                        , ('PQTD'    , 'Previous Quarter to Date'        , DATEADD(qq, y.DiffQQ - 1, 0)    , DATEADD(qq, -1, t.Today)            )
                        , ('PYQ'    , 'Previous Year Quarter'            , DATEADD(qq, y.DiffQQ - 4, 0)    , DATEADD(qq, y.DiffQQ - 3, 0) - 1    )
                        , ('PYQTD'    , 'Previous Year Quarter to Date'    , DATEADD(qq, y.DiffQQ - 4, 0)    , DATEADD(yy, -1, t.Today)            )
                        , ('PY'        , 'Previous Year'                    , DATEADD(yy, y.DiffYY - 1, 0)    , DATEADD(yy, y.DiffYY, 0) - 1        )
                        , ('PYTD'    , 'Previous Year to Date'            , DATEADD(yy, y.DiffYY - 1, 0)    , DATEADD(yy, -1, t.Today)            )

                        , ('L7D'    , 'Last 7 days'                        , t.Today - ( 7 - 1)            , t.Today                            )
                        , ('L14D'    , 'Last 14 days'                    , t.Today - (14 - 1)            , t.Today                            )
                        , ('L21D'    , 'Last 21 days'                    , t.Today - (21 - 1)            , t.Today                            )
                        , ('L28D'    , 'Last 28 days'                    , t.Today - (28 - 1)            , t.Today                            )
                        , ('L30D'    , 'Last 30 days'                    , t.Today - (30 - 1)            , t.Today                            )
                        , ('L60D'    , 'Last 60 days'                    , t.Today - (60 - 1)            , t.Today                            )
                        , ('L90D'    , 'Last 90 days'                    , t.Today - (90 - 1)            , t.Today                            )

                    --    , ('L2M'    , 'Last 2 months'                    , DATEADD(mm, y.DiffMM - 2, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
                    --    , ('L3M'    , 'Last 3 months'                    , DATEADD(mm, y.DiffMM - 3, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
                    --    , ('L4M'    , 'Last 4 months'                    , DATEADD(mm, y.DiffMM - 4, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
                    --    , ('L5M'    , 'Last 5 months'                    , DATEADD(mm, y.DiffMM - 5, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
                    --    , ('L6M'    , 'Last 6 months'                    , DATEADD(mm, y.DiffMM - 6, 0)    , DATEADD(mm, y.DiffMM, 0) - 1        )
            ) x(Code, [Period], BeginDate, EndDate)
        WHERE x.Code = @DateCode OR @DateCode IS NULL

        RETURN
    END
    GO

Viewing 6 posts - 46 through 51 (of 51 total)

You must be logged in to reply to this topic. Login to reply