Clean up a table & save to another table

I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id

> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT

> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John

> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)