[Download-List] question on making a report for vendors that have 3 or more checks since a date...?

[David L. Rotman]

I believe this is one area where UniQuery is missing some functionality
that exists in most SQL implementations.  I don't know of any way to
select separate records based on a calculation across records.
 
As a work around, we have used two different approaches:
* create an intermediate (temporary) file that is keyed by vendor and
contains either a list of the checks issued within your date range or a
count of those checks (look at programs like CREATE.SUMMARY on our FTP
site for a utility that can create these records)
* create an i-descriptor on your vendors file that fetches all checks
written to that vendor...then use TRANS to get the check dates.  Once
you have the dates, you can use a subroutine like IF.COMPARE (also on
our FTP site) to find all of the checks within a particular date range.
 
I'm open to suggestions if someone has found an easier way to do this
kind of select.
 
 
 
Dave Rotman
Associate Vice President for Technology
Chief Information Officer
Cedarville University
251 N. Main Street
Cedarville, OH 45314
rotmand at cedarville.edu
voice 937-766-7905
fax 937-766-8819
 


>>> On 12/1/2011 at 2:52 PM, in message
<8B65C5E2BF77A646B389712DB2611C195105552739 at mail.campus.collegeofidaho.edu>,
"Johnson, Keith M" <KMJohnson at collegeofidaho.edu> wrote:


Hi all,
I have been toying with making a report that only shows vendors that
have three checks since our current FY.
 
The CC XCOUNTER just returns a numeral ‘1’ per check record.  Other
than going in to a subr can this be done at the DB level?   I think I
have done it years ago but cannot think of the way it’s done…
 
SELECT CHECKS WITH CHK.DATE GT '07/01/11' BY CHK.VENDOR AND WITH EVAL
SUM 'XCOUNTER' GE 3            # blows up
 
LIST CHECKS BREAK.ON CHK.VENDOR EVAL "IF (TOTAL XCOUNTER) GT 3 THEN
TOTAL XCOUNTER" DET.SUP NOPAGE   # blows up
 
 
These two base lines get me my report but include the vendors with only
one or more checks since my date.
=========
SELECT CHECKS WITH CHK.DATE GT '07/01/11' BY CHK.VENDOR
LIST CHECKS BREAK.ON CHK.VENDOR TOTAL XCOUNTER DET.SUP NOPAGE
 
 
Ultimately I was thinking of doing something like the download
example…
DOWNLOAD.TEST.PARAGRAPH
[…]
161: DISPLAY
===============================================================
162: DISPLAY 10 Testing EVAL and COL.HDG using comma-quote output
163: SELECT DOWNLOAD.TEST.FILE \
[…]
168:   EVAL "NUMERIC.FIELD+58" \
169:   COL.HDG "New Amount" \
 
TIA  -KJ
 
Keith Johnson
Information Technology                                   
The College of Idaho                                      
kmjohnson at collegeofidaho.edu
2112 Cleveland Blvd Caldwell, ID 83605          
http://www.collegeofidaho.edu
Business: 208.459.5209
Fax: 208.459.5543
 
CONFIDENTIALITY NOTICE: This e-mail message is intended only for the
use of the individual or entity to which it is addressed and may contain
information that is privileged, confidential and exempt from disclosure
under applicable law. If you believe you have received this e-mail in
error please notify the sender. 
 
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.cedarville.edu/pipermail/download-list/attachments/20111202/7cc6a866/attachment.html>