I have several tables that contains info for my customer. The tblCustomer table contains keys for the data from other tables. I need to perform calculations based on the values stored in the other tables here is the database structure and some of the data.
Any assistance would be most helpful. ???
there are 3 calculations, I will be performing a search by date range to get my dataset from tbltranslog:
- based on the SubID in tbltranslog I need calculate the "Fee" based on the value that is set in tblCustomer, I need to do this for each transaction that is in tbltranslog
- calculate the check fee for each item that is tbltranslog that is a check, again the rate for the check is set in the tblCustomer.
- calculate the credit card fee for each item that is tbltranslog that is a credit card, again the rate for the check is set in tblCustomer
CREATE TABLE tblCustomer (
Customer_ID int(5) NOT NULL auto_increment,
AcctID varchar(8) NOT NULL,
SubID varchar(8) NOT NULL,
Merchant varchar(32) NOT NULL,
Status varchar(8) NOT NULL,
Address1 varchar(32) NOT NULL,
Address2 varchar(32) NOT NULL,
City varchar(32) NOT NULL,
State varchar(20) NOT NULL,
Zip varchar(10) NOT NULL,
Phone varchar(12) NOT NULL,
Fax varchar(12) NOT NULL,
Email varchar(32) NOT NULL,
Contact varchar(32) NOT NULL,
BankID varchar(20) NOT NULL,
AuthNet varchar(20) NOT NULL,
MID varchar(20) NOT NULL,
TID varchar(20) NOT NULL,
TermKey_ID varchar(32) NOT NULL,
CCFees_ID varchar(4) NOT NULL,
CKFees_ID varchar(4) NOT NULL,
Fee varchar(20) NOT NULL,
MCSAcct varchar(19) NOT NULL,
DebitAcct varchar(50) NOT NULL,
PaymentType varchar(16) NOT NULL,
AVS varchar(4) NOT NULL,
CVV2 varchar(3) NOT NULL,
Reserve varchar(4) NOT NULL,
Payout varchar(4) NOT NULL,
Pricing_ID varchar(4) NOT NULL,
PRIMARY KEY (Customer_ID)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Dumping data for table tblCustomer
INSERT INTO tblCustomer (Customer_ID, AcctID, SubID, Merchant, Status, Address1, Address2, City, State, Zip, Phone, Fax, Email, Contact, BankID, AuthNet, MID, TID, TermKey_ID, CCFees_ID, CKFees_ID, Reseller, MCSAcct, DebitAcct, PaymentType, AVS, CVV2, Reserve, Payout, Pricing_ID) VALUES (12, 'ABCD1', '10001', 'Eric''s Test Acco', '1000', '123 some street', '', 'Some Town', 'Some State', '33137', '212-555-1212', '212-555-1212', 'one@metallica.com', 'Eric', 'My Bank', '1006', '12345', '12345', '1234-12', '1009', '1001', 'No', '', '12345', 'ACH', 'Yes', 'Yes', '1000', '1002', '');
CREATE TABLE tblCCFees (
CCFees_ID varchar(4) NOT NULL,
Rate varchar(5) NOT NULL,
PRIMARY KEY (CCFees_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table tblCCFees
INSERT INTO tblCCFees (CCFees_ID, Rate) VALUES ('1000', '.25'),
('1001', '.30'),
('1002', '.35'),
('1003', '.40'),
('1004', '.45'),
('1005', '.50'),
('1006', '.55'),
('1008', '1.00'),
('1007', '.75'),
('1009', '1.25'),
('1010', '1.35'),
('1012', '1.45'),
('1013', '1.50');
INSERT INTO tblCKFees (CKFees_ID, Rate) VALUES ('1000', '1.00'),
('1001', '.75'),
('1002', '1.25'),
('1003', '1.50');
INSERT INTO tblReserveRate (ReserveRate_ID, Reserve) VALUES (1000, '10.00'),
(1010, '9.0'),
(1011, '9.25'),
(1012, '9.5'),
(1013, '9.75'),
(1014, '8.0'),
(1015, '8.25'),
(1016, '8.5'),
(1017, '8.75');
CREATE TABLE tbltranslog (
TransLog_ID int(10) NOT NULL auto_increment,
SubID int(7) NOT NULL,
Date char(21) NOT NULL,
Amount varchar(9) NOT NULL,
TransType varchar(15) NOT NULL,
TransResult varchar(8) NOT NULL,
OrderNo varchar(10) NOT NULL,
TransKey varchar(10) NOT NULL,
PRIMARY KEY (TransLog_ID),
UNIQUE KEY OrderNo (OrderNo)
) ENGINE=InnoDB AUTO_INCREMENT=10479 DEFAULT CHARSET=latin1 AUTO_INCREMENT=10479 ;
--
-- Dumping data for table tbltranslog
INSERT INTO tbltranslog (TransLog_ID, SubID, Date, Amount, TransType, TransResult, OrderNo, TransKey) VALUES (10095, 10001, '12/05/2006 20:48:43', '25.00', 'Sale', 'Approved', '53807312', '68295959');
INSERT INTO tbltranslog (TransLog_ID, SubID, Date, Amount, TransType, TransResult, OrderNo, TransKey) VALUES (10094, 10001, '12/05/2006 18:56:04', '29.99', 'Check Pre-Auth', 'Approved', '53805218', '68293514');
INSERT INTO tbltranslog (TransLog_ID, SubID, Date, Amount, TransType, TransResult, OrderNo, TransKey) VALUES (10093, 10001, '12/05/2006 18:50:06', '29.99', 'Sale', 'Approved', '53805092', '68293359');
