November 3, 2015 at 10:06 pm
I have two sample tables, customer_payment and customer_info. I hope to compete the following table for all domestic(USA) transactions, that is aggregation total number of Loans and total amount of payment in each state with each payment_type (Check, Wire or ACH)
Payment_Type | State | Num_Trans | Trans_Vol
CHECK | CA
CHECK | IL
CHECK | IA
WIRE | CA
WIRE | IL
WIRE | IA
ACH | CA
ACH | IL
ACH | IA
CREATE TABLE customer_payment
(
Customer_Nbr_TxtVARCHAR(255),
Loan_NbrBIGINT,
Trans_Amt decimal(22,2),
Payment_tp VARCHAR(255)
)
INSERT INTO customer_payment
VALUES
('007849', '11613577', '1000','ACH'),
('007849', '11613578', '200', 'ACH'),
('007849', '11613579', '450', 'ACH'),
('007849', '11613580', '123', 'CHECK'),
('008120', '14364941', '321', 'ACH'),
('008120', '14364942', '330', 'WIRE'),
('008120', '14364946', '1587', 'WIRE'),
('008120', '14364942', '1000', 'WIRE'),
('009522', '12345678', '331', 'CHECK'),
('009522', '10234567', '32', 'WIRE')
CREATE TABLE customer_info
(
Customer_Nbr_TxtVARCHAR(255),
Address_CountryVARCHAR(255),
Address_State VARCHAR(255)
)
INSERT INTO customer_info
VALUES
('007849', 'USA', 'IL'),
('008120', 'USA', 'IA'),
('009522', 'USA', 'CA'),
('000001', 'USA', 'CA'),
('000019', 'USA', 'PA'),
('000025', 'CAN', 'BC')
November 4, 2015 at 5:34 pm
Is this homework or similar? If so, we can only give you general guidelines, not fully completed SQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply