ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Need report for expiring contract prices
http://www.istechforum.com/YaBB.pl?num=1332881658

Message started by Specprod on 03/27/12 at 13:54:18

Title: Need report for expiring contract prices
Post by Specprod on 03/27/12 at 13:54:18

Need report for contract prices that would be expiring over a certain date. Ideally I would like to set up a report that I can search on a company and it would show me pricing that has either expired or is about to expire.

What would be the cost for such a report?

Currently we have Crystal Reports version XI

EVO 1/25/12

Title: Re: Need report for expiring contract prices
Post by Kelloggs on 03/27/12 at 14:49:29

SELECT *
FROM BKICPMAT
WHERE BKIC_PMAT_EDATE >= CURDATE ( ) AND BKIC_PMAT_EDATE <= (CURDATE ( )+ 14)

contract prices that will expired in the next 14 Days

:)

Kelloggs

PD: This is missing the Customer Name, but I will give you an idea, you can even use ms excel.
:-X

Title: Re: Need report for expiring contract prices
Post by Kelloggs on 03/29/12 at 09:10:43

There is a problem here, in order to create a report on a table format, we have to "Normalized" the "BKICPMAT" table.
DBA tables where created in 1988? 1987?

This is what I mean: http://en.wikipedia.org/wiki/Database_normalization

The BKICPMAT table has the following structure:

SELECT
BKICPMAT.BKIC_PMAT_CUST,
BKICPMAT.BKIC_PMAT_PCODE,
BKICPMAT.BKIC_PMAT_PNUM,
BKICPMAT.BKIC_PMAT_RATE_1,
BKICPMAT.BKIC_PMAT_RATE_2,
BKICPMAT.BKIC_PMAT_RATE_3,
BKICPMAT.BKIC_PMAT_RATE_4,
etc, etc, etc

For each line there is a corresponding field on the table. That shouldn't be.

Any ways the reports is possible.

:P

Kelloggs