INSERT Help

Last Post 12 Jul 2005 12:41 PM by g8r. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Pologuy67
New Member
New Member

--
12 Jul 2005 09:46 AM
Howdy Folks,

Need help with an INSERT INTO statement.
OldPayments
ID | CLN | PLN | C | Amt | CT | INVNUM
1 | 1 | 1 | IC | 30 | 6 | 23236
2 | 1 | 2 | TPA | 15 | 5 | 23236
3 | 1 | 1 | IC | 30 | 6 | 23236
4 | 1 | 2 | TPA | 15 | 5 | 23236
5 | 2 | 1 | IC | 20 | 6 | 23236
6 | 2 | 2 | TPA | 10 | 5 | 23236
7 | 1 | 1 | TPA | 5 | 5 | 12345
8 | 1 | 1 | IC | 55 | 6 | 52145
9 | 1 | 2 | TPA | 30 | 5 | 52145

As you can see lines 3 and 4 are dupes of 1 and 2.... Need to weed those out.

What I'm trying to do is INSERT INTO Payments Structure below from stuff above.

Payments
PayID | PayTypeID | PayAmt | AdjID | AdjAmt | InvNum
1 | IC | 30 | TPA | 15 | 23236
2 | IC | 20 | TPA | 10 | 23236
3 | NULL | NULL | TPA | 5 | 12345
4 | IC | 55 | TPA | 30 | 52145

Thanks,
Wayne



g8r
New Member
New Member

--
12 Jul 2005 12:41 PM
You could select distinct all the rows out into a work table, without using the ID field, delete the original table then reinsert the rows from the work table back into the original table.
You didn't say if the ID col was identity or not so there may be other issues but that's one way of getting around the problem...
mwesch
New Member
New Member

--
12 Jul 2005 03:02 PM
Sorry, but I do not see the correlation between OldPayments and Payments. I see the duplicates you mention, but not how you convert the data structures.
Pologuy67
New Member
New Member

--
13 Jul 2005 05:36 AM
Line 1 is a payment made against a row in another table. CT = 6
Line 2 is an adjustment made against the same row. CT = 5

I'm looking to create a single row from 1 and 2 to make a complete thought.


Thanks for replying,
Wayne
mwesch
New Member
New Member

--
13 Jul 2005 06:06 PM
I've done similar transactional conversions, so I think I can help. Have some more questions though.

1. Are associated payments (IC) and adjustments (TPA) always sequential by ID?

2. Do IC records always come before the TPA record?

3. What is the meaning of an adjustment with no payment?

4. Any significance to CLN and PLN?

Pologuy67
New Member
New Member

--
03 Aug 2005 09:44 AM
Sorry Mike,

Been a bit crazy around here. Answer to 1 is Yes.

Answer to 2 is If there is one yes but you wont always have and IC, sometimes just a TPA.

Answer to 3 is Sometimes you will adjust off part of a charge without ever receiving payment i.e. a professional courtesy.

Answer to 4 is CLN/PLN is basically a one to many relationship, ChargeLineNumber(CLN) 1 can have multiple PaymentLineNumbers(PLN)


mwesch
New Member
New Member

--
03 Aug 2005 03:19 PM
So does (INVNUM, CLN, PLN) make up a unique key?
You are not authorized to post a reply.

Acceptable Use Policy