March 31, 2017 at 2:49 pm
My fiscal year calculation is wrong. Give me a couple of minutes to fix it.
March 31, 2017 at 2:58 pm
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
April 1, 2017 at 10:42 am
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!
April 10, 2017 at 6:33 pm
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)
May 11, 2017 at 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.
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.
May 17, 2017 at 4:48 pm
Stan Zos - Thursday, May 11, 2017 10:53 PMGreat 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 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply