Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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

endMethod
By 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.LongDow
or 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   4 
You 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.