Calculating the Number of Days Between Dates

Suppose you want to know how long it took to ship an item. That is, you need to calculate the number of days between the date an item was ordered and when it was shipped. Calculating the number of days between dates is easy in Stonefield Query: just create a formula that subtracts the two date fields, such as:

ShippedDate – OrderDate

However, there may be a few complications with this.

Handling null dates

What should the number of days be if the item hasn’t shipped yet? If ShippedDate is null (that is, an unknown value), ShippedDate – OrderDate is also null, which displays as blank in Stonefield Query. If that’s what you want, great. If not, adjust the formula to display a special value in that case, such as:

IIF(ISNULL(ShippedDate), DATE(), ShippedDate) – OrderDate

which displays the number of days between the order date and today if the item hasn’t shipped, or:

IIF(ISNULL(ShippedDate), -1000, ShippedDate – OrderDate)

which displays –1000.

Note that you can’t use something like:

IIF(ISNULL(ShippedDate), "Not shipped", ShippedDate – OrderDate)

because the formula is supposed to be numeric but “Not shipped” isn’t numeric.

Handling weekends

Do you ship on weekends? If not, the number of days it took to ship an item may be overstated because the formula counts days even when you’re not open. For example, if something was ordered on Friday and shipped on Monday, the difference is three days when it should really be one.

In that case, you need a more complicated formula, one that subtracts weekend days. To do that, use an expression of:

WeekendSpan(OrderDate, ShippedDate)

After you tab out of the Expression textbox in the Formula Editor, Stonefield Query tells you that a function named WeekendSpan can’t be found and asks if you’d like to create it. Choose Yes and paste the following code into the code editor window that appears:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
      ldDate = tdStart + lnI
      lnDay  = dow(ldDate)
      if lnDay = 1 or lnDay = 7
            lnWeekendDays = lnWeekendDays + 1
      endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

This code assumes you’re closed on Saturday and Sunday; the DOW function gives the day of the week for the specified date, which is 7 for Saturday and 1 for Sunday. Adjust the code as necessary if, say, you’re closed on Friday and Saturday and open on Sunday.

Handling holidays

This code doesn’t account for holidays, such as December 25 or January 1. To check for dates like that, change the code to:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
    ldDate = tdStart + lnI
    lnDay  = dow(ldDate)
    if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate)
        lnWeekendDays = lnWeekendDays + 1
    endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

function IsHoliday(tdDate)
local laHolidays[2], llHoliday, lnI
laHolidays[1] = date(2015, 12, 25)
laHolidays[2] = date(2016, 1, 1)
llHoliday     = .F.
for lnI = 1 to alen(laHolidays)
    if month(laHolidays[lnI]) = month(tdDate) and ;
        day(laHolidays[lnI]) = day(tdDate)
        llHoliday = .T.
        exit
    endif
next
return llHoliday

(New code is shown in bold.)

Note that you have to dimension the laHolidays array to the number of holidays and set each element in the array to the appropriate date (don’t worry about the year part of the date; the code only checks month and day) as this example code does.

If you need to handle holidays that aren’t on fixed days, such as Thanksgiving in the U.S., you need to code for that specifically, such as:

if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate) or ;
    ldDate = DATE(2015, 11, 26)

Conclusion

As you can see, date math can be very simple or it can be more complicated, depending on your needs. However, it’s good to know that Stonefield Query can handle even the most complex date calculations you need.

Creating an Aging Report Using Stonefield Query

An aging report is one that shows totals broken down by age range. The most common example is an accounts receivable aging report, which shows how much each customer owes by period, typically within the past 30 days, 31 – 60 days, 61 – 90 days, and over 90 days. This type of report allows you to make business decisions, such as which customers to increase collection efforts with and which to cut off sales until they pay up (no point in continuing to sell products or do work for customers who won’t pay for them).

You’ve always been able to create this type of report in Stonefield Query, but until version 5.0, it was a bit of work. There were two ways to do it:

• Create a formula for each aging range that decides whether to include the amount or not using the IIF function that checks the date. For example, the expression IIF(BETWEEN(Invoices.DateDue, DATE(), DATE() – 30), Invoices.OutstandingAmount, 0) checks whether the due date for the invoice is between the current date and 30 days ago. If so, it takes the unpaid amount; if not, it takes 0. The report is grouped on customer and the formula fields are summed.
• Using the Advanced Report Designer, add fields to the report that do similar calculations to the formula approach.

A better way

A new feature added in version 5.0 called grouping formulas makes this a much simpler task. Here are the steps to creating an aging report using the sample data that comes with the Stonefield Query SDK; for other versions, such as Stonefield Query for Sage 300 ERP, the steps are very similar but you use the appropriate tables and fields instead of the ones mentioned here.

The first step is to create a quick report. Add the Customer Name field to the report, then click the New Formula button. Select the Customers table because that’s where we want the formula to go. Enter “0 – 30” for the Name and Heading since this first formula is for the first aging. Click the Expression Builder button (the one with the three dots) and select the Total Price field from the Order Details table because that’s the field we want to sum up.

Now here’s the key: we want this to be a grouping formula. A grouping formula is similar to a regular formula but it automatically summarizes the values, grouping on a certain field, and optionally for only a certain range of records. (Those of you who are technically oriented may guess that a grouping formula results in a SQL statement like SELECT SUM(SomeField) FROM SomeTable WHERE SomeConditions GROUP BY SomeGroupField.) In our case, we want to sum the total price, group the sums by customer, and only for records that are 30 or fewer days old. (Since the sample database doesn't have payments, we'll just assume that all orders are outstanding for demo purposes.)

To make this a grouping formula, click the Grouping button. Select Sum for Summary and Customer ID for Grouping Field. Click the Filter button, add a condition, select the Order Date field from the Orders table, and choose “is between” for the operator. The values to use are a little tricky: we don’t want to hard-code them to something like 05/01/2015 and 05/31/2015 because every time we run the report, we’d have to edit the formula and change the date range. We could turn on Ask at Runtime to prompt for the date range, but since we’ll ultimately have four formulas (one for each aging range), we don’t want to be asked for four different ranges of values; we really just want to put in a single value, which is the “as of” date for the aging calculations. To do that, click the More button, change Compare To to “Expression”, and enter the following expressions for the two values:

GetValueForParameter('As of date', 'D') – 30
(for the first value)

GetValueForParameter('As of date', 'D')
(for the second one)

GetValueForParameter is a built-in function that asks the user running the report for a value. The first parameter for the function is the text to display (“As of date” in this case) and the second is the data type for the value (“D” means we want a date; see the documentation for GetValueForParameter for what codes to use for different data types). The first expression tells Stonefield Query to ask the user for a date value and then subtract 30 days from that value. You may think from the second expression that Stonefield Query asks the user a second time, but GetValueForParameter has a nice feature: if the prompt is the same as a previous instance, the function doesn’t ask the user a second time but instead just returns the value they entered when they were asked. Since the two expressions both use “As of date” for the prompt, the user is asked only once. In fact, you’ll use a similar expression for the other formulas, such as 31 – 60, but subtract a different number of days, and since all of them use “As of date” for the prompt, the user is still only asked one time for the value that all expressions use.

Once you’ve saved the filter condition, the Grouping Formula Properties dialog should look like this:

Specify the formatting for the formula (I turned on Display $ and set Decimal Places to 2) and save the formula.

Finishing the report

Repeat these steps to create the 31 – 60, 61 – 90, and More Than 90 formulas. The only difference is the expressions used for the filter condition. For example:

GetValueForParameter('As of date', 'D') – 60
(first value for 31 – 60 formula)

GetValueForParameter('As of date', 'D') – 31
(second value for 31 – 60 formula)

(The expressions to use for the other two formulas are left as an exercise for the reader, but should be pretty obvious).

That’s it! Your report should now include Customer Name and the four formulas. No need to group the report on Customer Name or turn on Summary Report. When you run the report, you’re asked for the date to use for the aging calculations. The result should look like this:

Summary

Grouping formulas are a very powerful new feature added in version 5.0 of Stonefield Query. There are tons of uses for them, one of them making it very easy to create a simple aging report.

Version 3.2 Features (Definite)

Stonefield Query Version 3.2 is in the planning stages. There are hundreds and hundreds of features we could add, however, we can't add them all.

We've narrowed down the list and broken them into 3 categories: small (1-4 days), medium (1-2 weeks), and large (> 2 weeks).

This list includes the "definite" features for the next release. See the next blog entry for a list of features still on the chopping block.

Target release date: 03/31/2008

Large (More than 2 weeks)

• “Negative” queries. (ie. Show me a list of customers that did not have any orders last year)

• Multiple detail bands. (ie. Give me a complete view of a company's activities, history, forecasted sales, and invoices.)

• Join dialog so user can change joins without changing SQL statement (ie. “include records even if no records” changes join to RIGHT OUTER). This is only done if negative query is just another join choice. Options would include: contacts who have activities, contacts whether they have activities or not, activities whether they have contacts or not, contacts with no activities with specified filter (ie. in date range), activities with no contacts with specified filter

Medium (1 – 2 weeks)

• Excel data-only output for cross-tabs

• Templates control default page size and orientation

• More information in Setup Dialog e.g. email information

• Advanced button in Label and Cross-Tab Wizard (e.g. gain access to customize SQL and top n)

• Support VFP 9 SP2 features, including rotation, dynamics, advanced page, etc.

Small (1 – 4 days)

• Test Syntax button on Expression Builder (Done)

• Default table for each data group (Done)

• Option to pass “ignore this value” for ask-at-runtime conditions on command line (Done)

• SQApplication.DataEngine.GetValuesForField support default values and Ignore this Condition option (Done)

• Deleting folder deletes all reports in that folder

• Option to turn on/off point labels for all series

• Import reports from REPORTS.DBF (another option in Import dialog)

• Option to export all reports from a folder or just selected reports. Change “Export Report” to “Export Selected Report” and “Export All Reports” to “Export Reports...” which brings up a dialog with choices for selected report, all reports, and a TreeView of all reports with checkboxes (selecting a folder selects all reports in that folder)

• Display proper page number when group resets to page 1

• Options to not create label for new field and to not auto-size field in Advanced Report Designer

• Don’t check user count or name when run from command line or schedule or else can’t run report when already logged into UI

• Dialog to edit properties for a folder (ie. security)

• Option for which databases a user can access in Maintain Users and Groups dialog

• Encrypt connection user ID and password in error log and error report

• Display Date Only setting in Advanced Report Designer: enables user to turn this off if turned on in data dictionary

• Save “database” selection in filter page if “use next time” checked for all users (currently doesn’t work for runtime)

• If user cancels in GetValuesForField dialog called from Select script, don’t give “no records match” error

• Workstation-only installer (generic name so no branding issues) or no workstation installation at all (ie. all runtime files in program folder)

• Option to put all fields in same table in one line of group header

• Implement new features in TeeChart 8

Please send us your feedback on these features. We want to know if these are the things you want in Query.