|
Query/400 Handles Zero Dates (Sort Of)
Published: June 28, 2006
Hey, Ted:
I have a file that has two dates that are stored in eight-digit numeric fields in YYYYMMDD format. Using Query/400, I wish to find the difference in days between the two dates. However, one of the date fields may contain values of zero. When I view the resulting data from the query on my display, Query/400 fills the difference field with plus signs if a date is zero. But when I direct the query's output to a disk file, the query ends abnormally. Is there a way to replace the zero date with some other value, such as the current date, the other date field, or a date far into the future?
--Robert
Let me offer my condolences, Robert. I'm sorry that you have to use Query/400, the product that IBM never got around to finishing, when there are so many better report tools on the market. I'm reminded of something my friend Cletus the Codeslinger is fond of saying: "Anybody who puts a Query/400 query into a production job stream should be shot and his lousy carcass thrown into the dumpster." Now that I've editorialized, let me answer your question.
Unlike real query products, Query/400 does not have any conditional capabilities. You cannot say, "if this condition is true, do this, or else do that." However, in certain circumstances, you can fake it out. Fortunately, yours is one such certain circumstance.
The trick is to load a numeric field with a value of 1 or 0, indicating whether a date is zero or not. You can use this 1 or 0 field as a multiplier to load another date into a field.
Let's say the two dates in the file are PROMISEDT (the date we promised to ship an order to a customer) and SHIPDT (the date the order was shipped.) The promise date is always a valid date, but the ship date is zero if the order has not shipped. These zero ship dates exemplify the type of problem you describe.
Let's write a query to determine the number of days between the promise date and the ship date. We'll use a positive number to indicate that the order shipped late, a negative number to indicate that the order shipped early, and zero will mean that the order shipped right on time.
What should we do with the zero ship dates? You mentioned three possibilities. I don't suppose it matters which one we consider first, so let's begin by treating zero dates as some date far into the future. Orders that have not shipped will stick out like a sore thumb because they will appear to be thousands of days late.
Here are the necessary result fields. The last one, which I cleverly named LATE, is the difference between SHIPDT and PROMISEDT. Notice that the column heading entry blank contains comments about the result fields. This is one way to include comments in a query. The real column headings are defined under the Specify report column formatting option.
Field Expression Column Heading Len Dec
SDNOT0 shipdt/(shipdt-0.0000001) 0=Ship date is zero 1 0
1=ship date is not
zero
SDIS0 (sdnot0-1)*(sdnot0-1) 0=Shp date is not 1 0
zero, 1=ship date is
zero
ADJSHIPDT shipdt+(sdis0*20390101) Adjusted ship date 8 0
If zero, use Jan 1,
2039 instead
PDIG digits(promisedt) Convert promise date
to alpha
PDATE date(substr(pdig,5,2)||'/'|| Convert promise date
substr(pdig,7,2)||'/'|| to date
substr(pdig,3,2))
SDIG digits(adjshipdt) Convert adjusted
ship date to alpha
SDATE date(substr(sdig,5,2)||'/'|| Convert adjusted
substr(sdig,7,2)||'/'|| ship date to date
substr(sdig,3,2))
LATE days(sdate)-days(pdate) Number of days shipm
ent was late
The first result field is SDNOT0 (ship date is not zero). I determined whether ship date is not zero (1) or is zero (0) by dividing the ship date by itself minus a very small number and storing the result in a one-digit field.
The second result field is SDIS0 (ship date is zero.) This is the opposite of the SDNOT0. That is, if SDNOT0 is 1, SDIS0 is 0, and vice versa. I determined this field by subtracting one from SDNOT0 and squaring the answer. I'll leave it to you to figure out why that works, if you're curious.
The third result field, ADJSHIPDT (adjusted ship date), takes one of two values. If ship date is not zero, the adjusted ship date is the ship date value. The calculation in this case is ship date plus zero times a constant 20390101, which yields the ship date. If ship date is zero, adjusted ship date is January 1, 2039. The calculation is a zero ship date, plus one times 20390101.
The next four fields convert adjusted ship date and promise date into date fields (i.e., fields of the date data type), using a two-step process. The first step converts the numeric date to character. The second converts a character date to the date type. Notice that my system uses MM/DD/YY format.
The last field, LATE, finds the number of days difference. The DAYS function converts a date into an ordinal value, with day 1 being January 1 of the year 1 A.D.
Here's the result of the query with enough fields to show you what's going on.
PROMISEDT SHIPDT SDNOT0 SDIS0 ADJSHPDT LATE
20060501 20060501 1 0 20060501 0
20060501 20060502 1 0 20060502 1
20060501 20060430 1 0 20060430 1-
20060501 00000000 0 1 20390101 11,933
20060430 20060601 1 0 20060601 32
Notice that the record with the zero ship date is obvious because of the large value in LATE.
Now for the second option. If you want to substitute the current date for a zero date, your report shows how late, or how early, unshipped orders are. Here is the same query, with the necessary modifications.
Field Expression Column Heading Len Dec
CURDT year(current(date))*10000+ Current date 8 0
month(current(date))*100+
day(current(date))
SDNOT0 shipdt/(shipdt-0.0000001) 0=Ship date is zero 1 0
1=ship date is not
zero
SDIS0 (sdnot0-1)*(sdnot0-1) 0=Shp date is not 1 0
zero, 1=ship date is
zero
ADJSHIPDT shipdt+(sdis0*curdt) Adjusted ship date 8 0
If zero, use current
date instead
PDIG digits(promisedt) Convert promise date
to alpha
PDATE date(substr(pdig,5,2)||'/'|| Convert promise date
substr(pdig,7,2)||'/'|| to date
substr(pdig,3,2))
SDIG digits(adjshipdt) Convert adjusted
ship date to alpha
SDATE date(substr(sdig,5,2)||'/'|| Convert adjusted
substr(sdig,7,2)||'/'|| ship date to date
substr(sdig,3,2))
LATE days(sdate)-days(pdate) Number of days shipm
ent was late
The first field retrieves the current date as an eight-digit number in YYYYMMDD format. The other difference between this version and the first one is in the way adjusted sales date is calculated. The January 1, 2039 literal has been replaced with the numeric current date. If the current date is June 21, 2006, the unshipped order is 51 days late, as the following query run shows.
PROMISEDT SHIPDT SDNOT0 SDIS0 ADJSHIPDT LATE
20060501 20060501 1 0 20060501 0
20060501 20060502 1 0 20060502 1
20060501 20060430 1 0 20060430 1-
20060501 00000000 0 1 20060621 51
20060430 20060601 1 0 20060601 32
The third possibility you mentioned is to substitute the promise date for a zero ship date. The assumption is that unshipped orders will be shipped on time.
The only change necessary is in adjusted ship date. If ship date is not zero, the calculation is the ship date plus zero times the promise date. If ship date is zero, the calculation is zero plus one times the promise date.
Field Expression Column Heading Len Dec
SDNOT0 shipdt/(shipdt-0.0000001) 0=Ship date is zero 1
1=ship date is not
zero
SDIS0 (sdnot0-1)*(sdnot0-1) 0=Shp date is not 1
zero, 1=ship date is
zero
ADJSHIPDT shipdt+(sdis0*promisedt) Adjusted ship date 8 0
If zero, use promise
date instead
PDIG digits(promisedt) Convert promise date
to alpha
PDATE date(substr(pdig,5,2)||'/'|| Convert promise date
substr(pdig,7,2)||'/'|| to date
substr(pdig,3,2))
SDIG digits(adjshipdt) Convert adjusted
ship date to alpha
SDATE date(substr(sdig,5,2)||'/'|| Convert adjusted
substr(sdig,7,2)||'/'|| ship date to date
substr(sdig,3,2))
LATE days(sdate)-days(pdate) Number of days shipm
ent was late
Here's the result of the query.
PROMISEDT SHIPDT SDNOT0 SDIS0 ADJSHIPDT LATE
20060501 20060501 1 0 20060501 0
20060501 20060502 1 0 20060502 1
20060501 20060430 1 0 20060430 1-
20060501 00000000 0 1 20060501 0
20060430 20060601 1 0 20060601 32
Well, Robert, it's not elegant, but it works. Good luck!
--Ted
|
