![]() |
![]() |
|
![]() |
Calendar Tables and Derived Date Information © 2003 Lawrence G. DiGiovanni Digico, Inc. www.digicoinc.com Acknowledgement The author would like to recognize Rick Kelly for his significant contribution in reviewing this article. Preface This article describes how to use calendar tables for obtaining derived date information for use in queries, reports, and forms. This article also demonstrates how to create and maintain a simple calendar table and also addresses some of the pros and cons of their use. Introduction How much business did we do last quarter compared to the same quarter the previous year...? How many orders do we generate on Saturdays and Sundays...? What is historically our heaviest business month across all years...? Who was our best sales rep each month for the last two years...? A frequent question on the Paradox Newsgroups is, "How do I summarize by day/month/year in a query/form/report?" This stems from a (IMHO) weakness of the product in extracting and summarizing by certain types of calculations. QBE does not provide for much in the way of dynamic date functions, Local SQL does not permit the aggregation across most types of calculations, and it can be difficult to apply groupings and linking in forms and reports using ObjectPAL calculations. The simple answer is to extract the data you need, then parse that intermediate table using ObjectPAL, and supply the specific date logic you want. This approach works, and is sometimes the fastest way from a processing standpoint, but it’s just not very flexible from an ad-hoc perspective. A more flexible approach (and the subject of this article) is to create a calendar table. Simply put, this is a table of dates where each record holds derived information for each date. By "derived date information" I mean any piece of data that can be derived from a single date, for instance, that date’s day of the week, month of the year, the calendar quarter that date falls on, and so on. Some of these details are generic and apply to any business type, like day of the week, month of the year, and so on. Other details are a function of your business cycle, like fiscal year, fiscal quarter, week numbers, payroll period, and other details that are associated with your specific business or industry. In this article, we’ll define such a calendar table, populate it, and show how to use it in queries and reports. We’ll also address the common concerns associated with their use and maintenance. The Calendar Table The calendar table is just a table that is either keyed on a date field, or has a unique secondary index on a date field. In this article, we’ll key on the date field. Calendar.db CalDate D* ; The date ShortDow A3 ; Day of week as Sun, Mon, ... LongDow A10 ; Day of week as Sunday, Monday... DowNum S ; Day of week as 1=Sun, 2=Mon, ... ShortMonth A3 ; Month name as Jan, Feb ... LongMonth A10 ; Month name as January, February MonthNum S ; Month number as 1=Jan, 2=Feb, ... YearNum S ; Year QuarterNum S ; Calendar quarter ShortQuarter A2 ; Quarter as 1Q, 2Q, 3Q, 4Q LongQuarter A6 ; Quarter as 1QYYYY...This is obviously only a partial list. If you needed to, you could also add week number of year, phase of the moon, payroll period, a flag that indicates whether or not the date is a business day, and other descriptors that are relevant to your specific needs. Note: Be sure when defining your own table not to use reserved words for column names, like date, month, year, etc. That’s why I chose the names used above. Astute readers will notice that the above table definition does not adhere to Normal Forms. This is by intent. The calendar table itself is not transactional and was modeled according to dimensional principles seen often used in Data Warehousing and OLAP applications. You can choose to normalize this table and derive the natural hierarchies from the time dimension - for instance, weeks, months, quarters, and years - into their own tables, where more qualifying detail may be added without redundancy. This can improve performance in table operations that only require the higher level hierarchy, but it can also degrade performance if multiple tables are used to include multiple hierarchies. More information on Data Warehousing is available in the article A Primer for Data Warehousing. This article includes some detail on alternate data modeling principals. Populating the Calendar table Populating this table is pretty easy for the above fields. You can copy the below code to a script and run it to create a calendar.db table in the working directory. WARNING: This code will overwrite any existing calendar.db table: method run(var eventInfo Event) ;**************************************** ; Author: Larry DiGiovanni, Digico, Inc. ; Date: March 2003 ; Purpose: Create a calendar table with ; derived date attributes ;**************************************** var calTC TCursor calTable Table sTableName String startDate Date curDate Date endDate Date dayCounter Smallint fullMonth Array[12] String shortMonth Array[12] String longDow Array[12] String quarNum Array[12] Smallint endVar ; Our table is calendar.db, which you can change as needed sTableName = "Calendar.db" ; enumerating these gives the option to supply different names fullMonth[1] = "January" fullMonth[2] = "February" fullMonth[3] = "March" fullMonth[4] = "April" fullMonth[5] = "May" fullMonth[6] = "June" fullMonth[7] = "July" fullMonth[8] = "August" fullMonth[9] = "September" fullMonth[10] = "October" fullMonth[11] = "November" fullMonth[12] = "December" shortMonth[1] = "Jan" shortMonth[2] = "Feb" shortMonth[3] = "Mar" shortMonth[4] = "Apr" shortMonth[5] = "May" shortMonth[6] = "Jun" shortMonth[7] = "Jul" shortMonth[8] = "Aug" shortMonth[9] = "Sep" shortMonth[10] = "Oct" shortMonth[11] = "Nov" shortMonth[12] = "Dec" longDow[1] = "Sunday" longDow[2] = "Monday" longDow[3] = "Tuesday" longDow[4] = "Wednesday" longDow[5] = "Thursday" longDow[6] = "Friday" longDow[7] = "Saturday" ; you could derive these as well, but not all quarters ; are calendar quarters. Some business start FYs in ; October or April, for instance quarNum[1] = 1 quarNum[2] = 1 quarNum[3] = 1 quarNum[4] = 2 quarNum[5] = 2 quarNum[6] = 2 quarNum[7] = 3 quarNum[8] = 3 quarNum[9] = 3 quarNum[10] = 4 quarNum[11] = 4 quarNum[12] = 4 calTable = create sTableName with "CalDate" : "D", ; The date "ShortDow" : "A3", ; Day of week as Sun, Mon, ... "LongDow" : "A10", ; Day of week as Sunday, Monday... "DowNum" : "S", ; Day of week as 1=Sun, 2=Mon, ... "ShortMonth" : "A3", ; Month name as Jan, Feb ... "LongMonth" : "A10", ; Month name as January, February "MonthNum" : "S", ; Month number as 1=Jan, 2=Feb, ... "YearNum" : "S", ; Year "QuarterNum" : "S", ; Calendar quarter "ShortQuarter": "A2", ; Quarter as 1Q, 2Q, 3Q, 4Q "LongQuarter" : "A6" ; Quarter as 1QYYYY KEY "CalDate" endCreate calTC.open(sTableName) calTC.edit() ; substitute necessary dates for start and end startDate = date(1,1,1999) curDate = startDate endDate = date(1,1,2004) ; loop between start date and end date while curDate < endDate calTC.insertRecord() calTC.CalDate = curDate calTC.ShortDow = format("DO(%W)", curDate) calTC.LongDow = longDow[dowOrd(curDate)] calTC.DowNum = dowOrd(curDate) calTC.ShortMonth = shortMonth[month(curDate)] calTC.MonthNum = month(curDate) calTC.LongMonth = fullMonth[month(curDate)] calTC.YearNum = year(curDate) calTC.QuarterNum = quarNum[month(curDate)] calTC.ShortQuarter = string(calTC.QuarterNum) + "Q" calTC.LongQuarter = calTC.ShortQuarter + string(year(curDate)) ; Additional custom logic could be added for business- or ; industry-specific attributes calTC.PostRecord() curDate = curDate + 1 ; comment this line out if you don't want the feedback message(curDate) endWhile ; Now create secondary indexes ; For the sake of brevity, I'm only indexing ; these four. There's no reason you couldn't ; or shouldn't index them all index calTable maintained on "DowNum" endIndex index calTable maintained on "MonthNum" endIndex index calTable maintained on "YearNum" endIndex index calTable maintained on "QuarterNum" endIndex endMethodBy modifying the above code, it’s possible to apply just about any logic required to define a derived attribute for any date that’s relevant to any application. You of course have to define the appropriate date range required for your organization, which is basically the earliest date you have business to some point years in the future. Using The Calendar table With the table defined that covers any date range you need, you now have the ability to join or link this table into any query, form, or report that requires filtering, displaying, or summarizing on any of the above derived attributes. Think of it this way: by joining or linking a date in your table with the CalDate column of the calendar table, you can include in your query or data model any of the derived attributes of your date field. For instance, to query a sales orders table: Orders.db OrdID I* OrderDate D CustID I OrderAmt $You can now summarize order totals, number of orders, and customers across any of the above attributes. For instance, to see the distribution of orders by day of week: SELECT c.DowNum, c.LongDow, COUNT(*) FROM orders o, calendar c WHERE o.orderdate = c.thedate GROUP BY c.DowNum, c.LongDow ORDER BY c.DowNum, c.LongDowor in QBE: orders | orderdate | | _dt, CALC COUNT ALL | calendar | caldate | downum | longdow | | _dt | Check | Check |Gives: 1 Sunday 2 2 Monday 60 3 Tuesday 58 4 Wednesday 62 5 Thursday 12 6 Friday 35 7 Saturday 4You can find out the number of days between any two dates, too: SELECT COUNT(*) FROM calendar WHERE TheDate BETWEEN <date1> AND <date2>You can also link the calendar table into reports and forms, and summarize on derived date attributes. The remaining examples were created based on the calendar.db table created above and the orders.db table included in the DBDemos download. Orders.db was used to create bigord.db, a orders table with 26,000 rows, by changing the key field to autoinc and adding to itself many times. We also join to the employees.db table in a couple of examples, also part of the DBDemos download. Example 1: Gross sales by year and month. Order by year and ordinal month. Query ANSWER: :PRIV:ANSWER.DB SORT: Calendar.db->"YearNum", Calendar.db->"MonthNum", bigord.DB->"ItemsTotal", Calendar.db->"ShortMonth" Calendar.db | CalDate | ShortMonth | MonthNum | YearNum | | _dt | Check | Check | Check | bigord.DB | SaleDate | ItemsTotal | | _dt | CALC SUM | EndQuery Example 2: Gross sales by day of week, ordered by ordinal day number. Query ANSWER: :PRIV:ANSWER.DB Calendar.db | CalDate | ShortDow | DowNum | | _dt | Check | Check | bigord.DB | SaleDate | ItemsTotal | | _dt | CALC SUM | EndQuery Example 3: Gross sales by year, ordered by gross sales. Query ANSWER: :PRIV:ANSWER.DB SORT: bigord.DB->"ItemsTotal", Calendar.db->"YearNum" Calendar.db | CalDate | YearNum | | _dt | Check | bigord.DB | SaleDate | ItemsTotal | | _dt | CALC SUM | EndQuery Example 4: Gross sales by employee by year, ordered by employee name. Query ANSWER: :PRIV:ANSWER.DB FIELDORDER: employee.DB->"FirstName", employee.DB->"LastName", Calendar.db->"YearNum", bigord.DB->"ItemsTotal" SORT: employee.DB->"LastName", employee.DB->"FirstName", Calendar.db->"YearNum", bigord.DB->"ItemsTotal" Calendar.db | CalDate | YearNum | | _dt | Check | bigord.DB | SaleDate | EmpNo | ItemsTotal | | _dt | _emp | CALC SUM | employee.DB | EmpNo | LastName | FirstName | | _emp | Check | Check | EndQuery Example 5: Gross sales by employee and quarter for 1993 sales only, order by employee name, quarter number. Query ANSWER: :PRIV:ANSWER.DB FIELDORDER: employee.DB->"FirstName", employee.DB->"LastName", Calendar.db->"YearNum", Calendar.db->"QuarterNum", bigord.DB->"ItemsTotal" SORT: bigord.DB->"ItemsTotal", employee.DB->"LastName", employee.DB->"FirstName", Calendar.db->"YearNum", Calendar.db->"QuarterNum" Calendar.db | CalDate | YearNum | QuarterNum | | _dt | Check 1993 | Check | bigord.DB | SaleDate | EmpNo | ItemsTotal | | _dt | _emp | CALC SUM | employee.DB | EmpNo | LastName | FirstName | | _emp | Check | Check | EndQuery Maintaining The Calendar table The biggest objection to using such a table is the perception that it creates a maintenance headache. It is true that this approach requires you to plan ahead and provide for it in your maintenance routines, and you’ll have to determine, as with any system feature, whether the benefit is outweighed by this and other costs. WARNING: If you do use this approach, failing to maintain this table for the complete date range required by your particular data will cause queries, forms, and reports that are based on it to produce erroneous results. Other Considerations The two other objections to using such a table are storage and performance - in other words, that the tables are too large to maintain, and that queries, forms, and reports that link or join such tables (rather than deriving the date info at runtime) are considerably slower. Note that 10 years worth of this data is fewer than 4,000 rows and will probably require less than 200k of storage. As was the case with the maintenance issue, you’ll have to assess the impact of storage. As far as performance is concerned, I can only suggest you try it and see. For some types of processing, direct, dynamic ObjectPAL will surely be faster. Note that since the calendar table is statically defined (except when it’s being maintained), you can add as many indexes as you need. Consider that the above QBE queries were executed on a calendar table with 15 years of data (1985-1999) and were executed against the derived Bigord.db table, which contained order information for 26,000 orders. On a PII450MHz 128MB RAM laptop running Paradox 10, none of the above queries took more than a second or two. Also note that this approach is widely used on many platforms and is a proven alternative to deriving this type of information via code at runtime. Summary The use of a calendar table is a valuable addition to many applications that can justify the minor storage and maintenance overhead it requires. This proven approach allows developers and end-users access to derived date information for analysis through queries, reports, and forms, as well as TCursor operations, drop-downs, and lookups. Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 03 Jun 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|