Welcome, Guest. Please Login.
03/28/24 at 23:26:16
News:
Home Help Search Login


Pages: 1
Send Topic Print
Need report for expiring contract prices (Read 1453 times)
Specprod
Browser
*




Posts: 9
Gender: male
Need report for expiring contract prices
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
Back to top
 
 
Email WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Need report for expiring contract prices
Reply #1 - 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
 
Smiley
 
Kelloggs
 
PD: This is missing the Customer Name, but I will give you an idea, you can even use ms excel.
 lips sealed
Back to top
 
« Last Edit: 03/28/12 at 07:18:59 by Kelloggs »  

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Need report for expiring contract prices
Reply #2 - 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.
 
 Tongue
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
Pages: 1
Send Topic Print