November 3, 2015 at 9:55 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 3, 2015 at 10:27 pm
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.customer_payment') IS NOT NULL DROP TABLE dbo.customer_payment;
IF OBJECT_ID(N'dbo.customer_info') IS NOT NULL DROP TABLE dbo.customer_info;
CREATE TABLE dbo.customer_payment
(
Customer_Nbr_TxtVARCHAR(255),
Loan_NbrBIGINT,
Trans_Amt decimal(22,2),
Payment_tp VARCHAR(255)
);
INSERT INTO dbo.customer_payment (Customer_Nbr_Txt,Loan_Nbr,Trans_Amt,Payment_tp)
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 dbo.customer_info
(
Customer_Nbr_TxtVARCHAR(255),
Address_CountryVARCHAR(255),
Address_State VARCHAR(255)
);
INSERT INTO dbo.customer_info(Customer_Nbr_Txt,Address_Country,Address_State)
VALUES
('007849', 'USA', 'IL'),
('008120', 'USA', 'IA'),
('009522', 'USA', 'CA'),
('000001', 'USA', 'CA'),
('000019', 'USA', 'PA'),
('000025', 'CAN', 'BC');
/* VIEW THE SET */
SELECT
CP.Customer_Nbr_Txt
,CP.Loan_Nbr
,CP.Trans_Amt
,CP.Payment_tp
,CI.Address_Country
,CI.Address_State
FROM dbo.customer_payment CP
INNER JOIN dbo.customer_info CI
ON CP.Customer_Nbr_Txt = CI.Customer_Nbr_Txt
/* AGGREGATION ON Payment_tp,Address_State */
SELECT
CP.Payment_tp
,CI.Address_State
,COUNT(*) AS Num_Trans
,SUM(CP.Trans_Amt) AS Trans_Vol
FROM dbo.customer_payment CP
INNER JOIN dbo.customer_info CI
ON CP.Customer_Nbr_Txt = CI.Customer_Nbr_Txt
GROUP BY CP.Payment_tp
,CI.Address_State
ORDER BY CP.Payment_tp
,CI.Address_State;
Result sets
Customer_Nbr_Txt Loan_Nbr Trans_Amt Payment_tp Address_Country Address_State
----------------- --------- ---------- ----------- ---------------- ---------------
007849 11613577 1000.00 ACH USA IL
007849 11613578 200.00 ACH USA IL
007849 11613579 450.00 ACH USA IL
007849 11613580 123.00 CHECK USA IL
008120 14364941 321.00 ACH USA IA
008120 14364942 330.00 WIRE USA IA
008120 14364946 1587.00 WIRE USA IA
008120 14364942 1000.00 WIRE USA IA
009522 12345678 331.00 CHECK USA CA
009522 10234567 32.00 WIRE USA CA
Payment_tp Address_State Num_Trans Trans_Vol
----------- -------------- ----------- -----------
ACH IA 1 321.00
ACH IL 3 1650.00
CHECK CA 1 331.00
CHECK IL 1 123.00
WIRE CA 1 32.00
WIRE IA 3 2917.00
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply