{"id":79630,"date":"2018-07-09T20:00:57","date_gmt":"2018-07-09T20:00:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=79630"},"modified":"2021-04-23T13:21:46","modified_gmt":"2021-04-23T13:21:46","slug":"t-sql-tuesday-104-code-you-would-hate-to-live-without-relative-positioning-in-date-table","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/t-sql-tuesday-104-code-you-would-hate-to-live-without-relative-positioning-in-date-table\/","title":{"rendered":"T-SQL Tuesday #104: Code You Would Hate To Live Without (Relative Positioning in Date Table)"},"content":{"rendered":"

\"T-SQL<\/p>\n

Today’s blog post is in response to Bert Wagner’s invitation here:\u00a0https:\/\/bertwagner.com\/2018\/07\/03\/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation\/<\/a>. For me, the answer was pretty simple. There is one piece of code that I have written that I find very useful, and even more… it was my (as far as I know, my own idea!)\u00a0 In my life, I have obviously learned a tremendous amount about building and designing databases on a relational engine. I have written books<\/a> about it, as you may know. While I am proud about this, all it generally means is that I took the work of so many other people and arranged it into 700 pages.<\/p>\n

The solution is part of my calendar\/date dimension code, and it is used to do relative positioning over date periods. For example, say you have the need to get data from the 10 days. You can definitely use a simple between to filter the rows, and a bunch of date functions to group by year, month, etc., generally all of the “normal” groupings. But using a calendar table allows you to prebuild a set of date calculations that make the standard values easier to get, and non-standard groupings possible. The technique I will cover makes moving around in the groupings more easily accessible. Like if you want data from the last 3 complete months. The query to do this isn’t rocket science, but it isn’t exactly straightforward either.<\/p>\n

For the example, I will use the calendar table that I have on my website here: https:\/\/drsql.org\/code<\/a> in the download SimpleDateDimensionCreateAndLoad, and will load it with data up until 2020. Here is that structure:<\/p>\n

CREATE TABLE Tools.Calendar\r\n(\r\n  CalendarId int NOT NULL CONSTRAINT PKdate_dim PRIMARY KEY ,\r\n  DateValue date NOT NULL CONSTRAINT AKdate_dim__DateValue UNIQUE , \r\n  DayName varchar(10) NOT NULL,\r\n  MonthName varchar(10) NOT NULL,\r\n  Year varchar(60) NOT NULL,\r\n  Day tinyint NOT NULL,\r\n  DayOfTheYear smallint NOT NULL,\r\n  Month smallint NOT NULL,\r\n  Quarter tinyint NOT NULL,\r\n  WeekendFlag bit NOT NULL, \r\n  DayInMonthCount tinyint NOT NULL,\r\n \r\n  --start of fiscal year configurable in the load process, currently \r\n  --only supports fiscal months that match the Calendar months.\r\n  FiscalYear smallint NOT NULL,\r\n  FiscalMonth tinyint NULL,\r\n  FiscalQuarter tinyint NOT NULL,\r\n \r\n  --used to give Relative positioning, such AS the previous 10 months\r\n  --which can be annoying due to month boundries\r\n  RelativeDayCount int NOT NULL,\r\n  RelativeWeekCount int NOT NULL,\r\n  RelativeMonthCount int NOT NULL \r\n);<\/pre>\n
You can join this table to your data, either on the CalendarId (which is a smart key like YYYYMMDD), or the actual date value (and you can change the key structure to meet your needs very simply. This is code I could not live without because I, and people I work with, do a lot of queries where we compare time periods, such as “compare this year’s sales period with 1, 2, and\/or 3 years ago.” and sales period does not correspond to any calendar that any other organization has created. We also commonly need to do this via a selectable parameter, where the user can decide the number of periods to compare.<\/span><\/p>\n
Now say you want to get the dates for the last 5 days. A typical (and definitely not wrong) thing you can do, is do date math on the current date and time using SYSDATETIME() or GETDATE(), though SYSDATETIME() is the newer alternative with extra precision that means nothing in this example:<\/p>\n
SELECT DateValue\r\nFROM Tools.Calendar\r\nWHERE DateValue BETWEEN DATEADD(DAY, -5, SYSDATETIME()) AND SYSDATETIME();<\/pre>\n
This returns:<\/p>\n
dateValue<\/code>
\n----------<\/code>
\n2018-07-01<\/code>
\n2018-07-02<\/code>
\n2018-07-03<\/code>
\n2018-07-04<\/code>
\n2018-07-05<\/code><\/p>\n
Which works well enough (though it is a bit concerning in a quick look that DateValue is a date and we are comparing it do datetime2(7) values, as shown, it will work as expected and include the current day and 4 previous days.) Now you can join to your table, such as a sales table:<\/p>\n
SELECT *\r\nFROM YourTable\r\n       JOIN Tools.Calendar\r\n            ON YourTable.DateColumn = Calendar.DateColumn\r\n            --or, if all you have is a column with a point in time\r\n            ON CAST(YourTable.PointInTime as date) = Calendar.DateColumn<\/pre>\n
It’s a small table, so joining on the function, while not ideal, will be better than the alternative of trying to calculate the various factors in the Calendar table row by row. Now you can filter and group by the year, month, fiscal month, etc.<\/p>\n
But now, what if you want something more complex. Like the previous last 3 full months? Using EOMONTH(), we could put together some criteria to make that happen for sure. Now consider any period of time where “real world” date math does not work at all. Like your corporate fiscal year, which doesn’t start on Jan 1. Or perhaps a fiscal month, which doesn’t start\/end on the first and last day of a month. You might even have time periods that are not contiguous, like sales periods (which is what I will give as example code).<\/p>\n
This is where my bit of code comes into play. Notice in the code it loaded:<\/p>\n
  RelativeDayCount int NOT NULL,\r\n  RelativeWeekCount int NOT NULL,\r\n  RelativeMonthCount int NOT NULL<\/pre>\n
Looking at the past 14 days, which cross a month and week boundaries because of when I am writing this (July 5):<\/p>\n
SELECT DateValue, RelativeDayCount, RelativeWeekCount, RelativeMonthCount\r\nFROM Tools.Calendar\r\nWHERE DateValue BETWEEN DATEADD(DAY, -14, SYSDATETIME()) AND SYSDATETIME();<\/pre>\n
This returns:<\/p>\n
DateValue\u00a0 RelativeDayCount RelativeWeekCount RelativeMonthCount<\/code>
\n---------- ---------------- ----------------- ------------------<\/code>
\n2018-06-22 96961 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13851 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0<\/span> 3185<\/code>
\n2018-06-23 96962 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13851 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-24 96963 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-25 96964 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-26 96965 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-27 96966 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-28 96967 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-29 96968 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-06-30 96969 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13852 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3185<\/code>
\n2018-07-01 96970 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13853 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3186<\/code>
\n2018-07-02 96971 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13853 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3186<\/code>
\n2018-07-03 96972 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13853 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3186<\/code>
\n2018-07-04 96973 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13853 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3186<\/code>
\n2018-07-05 96974 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>13853 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3186<\/code><\/p>\n
Notice the RelativeDayCount is a unbroken sequence. The week counts include 7 days for 13852 (this started counting from 1753-01-01 for some very arbitrary, long forgotten, reason), and the month boundary changes on the first. By using these groupings, which are sortable, we can use simple integer comparisons, typically using equality or between. Like if we want the days of the previous week:<\/p>\n
--get the current focal point (note that this value doesn't change in my examples. You could update the \r\n--calendar constantly such that 0 is always the current value, if desired\r\nDECLARE @currentWeek int = (SELECT RelativeWeekCount \r\n                            FROM Tools.Calendar \r\n                            WHERE dateValue = CAST(SYSDATETIME() AS date))\r\n\r\n--then get the rows where the values are 1 less than the current\r\nSELECT DayName, DateValue\r\nFROM Tools.Calendar\r\nWHERE RelativeWeekCount = @currentWeek - 1<\/pre>\n
The current date is July 5, and the first day of this month was July 1. So you can see the previous week ended on the 30th.<\/p>\n
DayName \u00a0\u00a0 DateValue<\/code>
\n---------- ----------<\/code>
\nSunday \u00a0 \u00a0 2018-06-24<\/code>
\nMonday \u00a0 \u00a0 2018-06-25<\/code>
\nTuesday \u00a0\u00a0 2018-06-26<\/code>
\nWednesday\u00a0 2018-06-27<\/code>
\nThursday \u00a0 2018-06-28<\/code>
\nFriday \u00a0 \u00a0 2018-06-29<\/code>
\nSaturday \u00a0 2018-06-30<\/code><\/p>\n
But as I said, the beauty of this technique is dealing with periods that cannot be done with simple date math. Lets set up a scenario of a company that needs to report on activity during given sales periods. I will add a couple of columns to set up this scenario. This company has a weekly sale that regularly lasts from Tues-Thurs, most weeks (on the week of June 17, the sale went from Thursday-Sunday for Father’s day instead.) You want to join to your data table to see sales on the day of the sale, as well as the days between each sale to see if a sale influenced following sales.<\/p>\n
So I am going to add 3 columns, the first is the relative positioning, and the last two are the kind of columns you would regularly wish to have in a calendar table to indicate a time period.<\/p>\n
ALTER TABLE Tools.Calendar\r\n        ADD RelativeSalePeriodCount int NOT NULL CONSTRAINT DFLTCalendar__RelativeSalePeriodCount DEFAULT (0),\r\n            ActualSaleDayFlag bit NOT NULL CONSTRAINT DFLTCalendar__ActualSaleDayFlag DEFAULT (0),\r\n            SaleName nvarchar(30) NOT NULL --You might make this not null, and default to 'No Current Sale'<\/pre>\n
Now, I will load the data, in a very manual manner, but if there was an algorithm that could easily be formulated, this technique probably would never have been born.\u00a0<\/p>\n
UPDATE Tools.Calendar\r\nSET SaleName = CASE WHEN DateValue BETWEEN '2018-05-05' AND '2018-05-07' THEN 'Sale 1'\r\n                    WHEN DateValue BETWEEN '2018-05-14' AND '2018-05-17' THEN 'Sale 2'\r\n                    WHEN DateValue BETWEEN '2018-05-19' AND '2018-05-21' THEN 'Sale 3'\r\n                    WHEN DateValue BETWEEN '2018-05-26' AND '2018-05-28' THEN 'Sale 4'\r\n               ELSE SaleName END, --Real world, you may make this 'Sale # After Period', but not germane to discussion\r\n    ActualSaleDayFlag = CASE WHEN DateValue BETWEEN '2018-05-05' AND '2018-05-07' THEN 1\r\n                             WHEN DateValue BETWEEN '2018-05-14' AND '2018-05-17' THEN 1\r\n                             WHEN DateValue BETWEEN '2018-05-19' AND '2018-05-21' THEN 1\r\n                             WHEN DateValue BETWEEN '2018-05-26' AND '2018-05-28' THEN 1\r\n                        ELSE 0 END,\r\n    RelativeSalePeriodCount = CASE WHEN DateValue BETWEEN '2018-05-05' AND '2018-05-13' THEN 1\r\n                                   WHEN DateValue BETWEEN '2018-05-14' AND '2018-05-18' THEN 2\r\n                                   WHEN DateValue BETWEEN '2018-05-19' AND '2018-05-25' THEN 3\r\n                                   WHEN DateValue >= '2018-05-26' THEN 4\r\n                              ELSE 0 END\r\nWHERE DateValue >= '2018-05-01';<\/pre>\n
So now, with this data you can query the data pretty easily and get the most recent sale by getting the value that is currently set, or get previous sale periods by moving to the previous boundary. For example, show me the dates for the previous two sales periods:<\/p>\n
--Fetch the current period count\r\nDECLARE @RelativeSalePeriodCount int = (SELECT RelativeSalePeriodCount \r\n                                        FROM Tools.Calendar \r\n                                        WHERE dateValue = CAST(SYSDATETIME() AS date));\r\n\r\n--then query for the past few by getting the previous 2 sales periods\r\nSELECT DateValue, DayName, SaleName, ActualSaleDayFlag, RelativeSalePeriodCount\r\nFROM Tools.Calendar\r\nWHERE RelativeSalePeriodCount BETWEEN @RelativeSalePeriodCount -2 AND @RelativeSalePeriodCount -1;<\/pre>\n
This returns the following:<\/p>\n
DateValue\u00a0 DayName \u00a0\u00a0 SaleName \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ActualSaleDayFlag RelativeSalePeriodCount \r\n---------- ---------- ------------------------------ ----------------- ----------------------- \r\n2018-05-14 Monday \u00a0 \u00a0 Sale 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 \r\n2018-05-15 Tuesday \u00a0\u00a0 Sale 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0   2\r\n2018-05-16 Wednesday\u00a0 Sale 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 \r\n2018-05-17 Thursday \u00a0 Sale 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 \r\n2018-05-18 Friday \u00a0 \u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 \r\n2018-05-19 Saturday \u00a0 Sale 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-20 Sunday \u00a0 \u00a0 Sale 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-21 Monday \u00a0 \u00a0 Sale 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-22 Tuesday \u00a0\u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-23 Wednesday\u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-24 Thursday \u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3 \r\n2018-05-25 Friday \u00a0 \u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3<\/pre>\n
From here, you should be able to pick out the data you want in your summaries. You just want to sum sales for days of the sale: ActualSaleDayFlag = 1. Non-sales days: ActualSaleDayFlag = 0. But getting a number of periods back is done simply by varying an integer factor.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"
Today’s blog post is in response to Bert Wagner’s invitation here:\u00a0https:\/\/bertwagner.com\/2018\/07\/03\/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation\/. For me, the answer was pretty simple. There is one piece of code that I have written that I find very useful, and even more… it was my (as far as I know, my own idea!)\u00a0 In my life, I have obviously learned a……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[60464,48392,4467,60463,4183],"coauthors":[19684],"class_list":["post-79630","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-calendar-table","tag-date","tag-date-arithmetic","tag-date-dimension","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=79630"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79630\/revisions"}],"predecessor-version":[{"id":90663,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79630\/revisions\/90663"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=79630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=79630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=79630"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=79630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}