Question

Global Maximum Date

  • 21 April 2021
  • 3 replies
  • 28 views

I want to create a view that will have multiple time bucketing options available for the end user, such as YTD, rolling 3 months, rolling 12 months, monthly, full year, etc.  To make this process dynamic, so that it doesn’t have to manually be recoded as new data rolls in, I want to structure this around the global maximum date.  

I’ve created a max date function but it only shows the max date for the column, not the global maximum.  Is there anyway to do this?


3 replies

Userlevel 6
Badge

Create a date table which is calculated every day using the current_date or now function depending on your database.

Could you expand upon creating a date table?  I’m not exactly sure what that is or what it entails.

Userlevel 6
Badge

You may already have one in your database:

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

you can dynamically in your view file then add on all those extra bits you talk of.

Reply