sql to get next id (SQL Server)

I have a tricky one.  

table name:Transactions
There are two types of TransactionType, claims and payments
In this table there is no existing field that links a claim to a payment so I need to create one. There is however, a field called ServiceID that links them all together
Usually the order of events is
ServiceID 1: Claim 1 --> Payment for 1 --> Claim 2 --> Payment for 2
     so the paymentID would just be the next transaction for that ServiceID. easy...
But sometimes it goes a bit off:
ServiceID 2:  Claim 3 --> Claim 4 --> Payment for 3 --> Payment for 4
    but, the order of the payments always relates to the order of the claims
    I can either assign the Claim ID to the Payment record or the Record ID to the Claim record, or both, whichever is easier.
KCTechNetAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
awking00Connect With a Mentor Commented:
Before you go back to the drawing board, you might try this -
select x.serviceid, x.claimtransactionid, y.paymenttransactionid from
(select transactionid as claimtransactionid, serviceid, transactiontype,
 row_number() over (partition by transactiontype order by transactionid) rn
 from transactions
 where transactiontype = 'Claim') as x,
(select transactionid as paymenttransactionid, serviceid, transactiontype,
 row_number() over (partition by transactiontype order by transactionid) rn
 from transactions
 where transactiontype = 'Payment') as y
where x.rn = y.rn;
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
What exactly is your question ?  Can you list your requirements exactly so that we can suggest a design.
0
 
KCTechNetAuthor Commented:
I need to create a query which would contain the transaction ID to link the payment transaction to the claim transaction.

So from my example above:

[ServiceID 1] , [Claim 1 Transaction ID]  , [Payment 1 Transaction ID]
[ServiceID 2],  [Claim 3 Transaction ID], [Payment 3 Transaction ID]
[ServiceID 2], [ Claim 4 Transaction ID], [Payment 4 Transaction ID]

I hope that explains things further.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please detail the transactions table columns.  Give some sample data and output.
0
 
KCTechNetAuthor Commented:
and to clarify the dilemma, I can't just grab the next PaymentID, because in the case of Claim 4, the next Payment in the time line is for Claim 3.  I need to grab the next ID that is not already "tied to claim"
0
 
KCTechNetAuthor Commented:
TransactionID  ServiceID   TransactionType
           1                         1                  Claim
           2                          2                 Claim
           3                          1                 Payment (for TransactionID 1)
           4                          2                 Claim
           5                          2               Payment (for TransactionID 2)
           6                          2               Payment (for TransactionID 4)

Output:
ServiceID       ClaimTransactionID     PaymentTransactionID
       1                          1                                                3
      2                            2                                                5
      2                           4                                                 6 (not 5)
0
 
KCTechNetAuthor Commented:
I just noticed that this is in the PHP area.  It should only be in SQL, sorry
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try the following...

select A.ServiceID, A.TransactionID as ClaimTransactionID, min (B.TransactionID) as PaymentTransactionID
(
select ServiceID, TransactionID
from Transactions  
where TransactionType='Claim'
)  A
inner join Transactions B 
on A.TransactionID=B.ServiceID
where B.TransactionType='Payment'
group by  A.ServiceID, A.TransactionID

Open in new window


Hope this helps...
0
 
Ray PaseurCommented:
Usually this sort of thing is handled with a "junction table" that contains keys of the related rows in the other data tables.  The junction table facilitates the relationship between the claims and payments (hence the name "relational" data base).
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
The junction table facilitates the relationship between the claims and payments (hence the name "relational" data base).
Actually the name relational for relational databases has nothing to do with relationships but stands for relations representing data as R-Tables.  In relational theory, relationships are simply physical pointers.
0
 
KCTechNetAuthor Commented:
unfortunately, the system does not have a "junction table".  that's what this query will become.
0
 
KCTechNetAuthor Commented:
Did you mean line 8 to read A.ServiceID = B.ServiceID ?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Did you mean line 8 to read A.ServiceID = B.ServiceID ?

Yes Sorry about the typo...

select A.ServiceID, A.TransactionID as ClaimTransactionID, min (B.TransactionID) as PaymentTransactionID
(
select ServiceID, TransactionID
from Transactions  
where TransactionType='Claim'
)  A
inner join Transactions B 
on A.ServiceID=B.ServiceID and A.TransactionID<B.TransactionID
where B.TransactionType='Payment'
group by  A.ServiceID, A.TransactionID

Open in new window


Hope this helps...
0
 
KCTechNetAuthor Commented:
I am  getting the same first Payment ID for all the claims as it is always returning the min(paymentID).  

But furthermore, even if I get the min(PaymentID) where b.TransactionID > A.TransactionID, this would give me the wrong ID in TransactionClaimiD 4.  In that case I would need 6, not 5, because 5 should be tied to ClaimTransactionID 2, not 4.

I am now trying to create a "sequence counter", but having syntax issues:
select ServiceID, TransactionID,
     (Select Count(1) From Transactions as tmp Where tmp.ServiceID = Transactions.ServiceID and Transactions.TransactionID <=tmp.TransactionID ) as ClaimSequenceNumber
from Transactions  
where TransactionType='Claim') as A

Open in new window


If I can get this to work, and do the same thing for B, then I can join on A.SequenceNumber = B.SequenceNumber.

But I get the message "The multi-part identifier Transactions.ServiceID could not be bound."
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
OK I see your problem now.  Working on it.
0
 
KCTechNetAuthor Commented:
I got it...

SELECT A.ServiceID, A.TransactionID AS ClaimID, A.SequenceNumber, B.TransactionID AS PaymentID
FROM
(SELECT (Select Count(1) From Transactions as tmp WHERE tmp.TransactionType= 'Claim' AND tmp.TransactionID<= Transactions .ID AND tmp.ServiceID= Transactions .ServiceID) AS SequenceNumber, [ID], ServiceID, TRAN_TYPE FROM Transactions WHERE TransactionType = 'Claim') AS A
Left JOIN
(SELECT (Select Count(1) From Transactions as tmp2 WHERE tmp2.TransactionType= 'Payment' AND tmp2.TransactionID <= Transactions .TransactionID AND tmp2.ServiceID= Transactions .ServiceID) AS SequenceNumber, [TransactionID], ServiceID, TransactionType FROM Transactions WHERE TransactionType= 'Payment') AS B

ON A.SequenceNumber = B.SequenceNumber  and A.ServiceID = B.ServiceID
order by ClaimID

Open in new window

0
 
KCTechNetAuthor Commented:
the "could not be bound" error was in my join on statement, not my subquery.
0
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
Switching the query structure to...

select A.ServiceID, 
A.TransactionID as PaymentTransactionID, 
max (B.TransactionID) as ClaimTransactionID
(
select ServiceID, TransactionID
from Transactions  
where TransactionType='Payment'
)  A
inner join Transactions B 
on A.ServiceID=B.ServiceID and A.TransactionID>B.TransactionID
where B.TransactionType='Claim'
group by  A.ServiceID, A.TransactionID
order by A.ServiceID, A.TransactionID

Open in new window


Hopefully the symmetry should help (I hope)
0
 
KCTechNetAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for KCTechNet's comment #a39273233
Assisted answer: 500 points for Racimo's comment #a39273256

for the following reason:

thanks for your time
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Have you tried to the last query ?  How did it go ?
0
 
KCTechNetAuthor Commented:
Your last one did not work either.  

For Service 2 yours returned:
Service   Claim  Payment
    2              4            5
    2             4             6

Where it should have been
   2              2             5
   2              4             6


You have Max(TransactionID) which returned ID 4 two times.

But now I find a scenario in the services where my rule of "always in the same sequence" is no longer true so I am back to the drawing board to find some rules that will work for every service...grrrr...

But points for your time though...thanks
0
 
KCTechNetAuthor Commented:
Actually it turns out there is just one group of payments that need to be ignored and I can go back to using my sequence numbers

But thanks for the row_number suggestion,...  I stumbled upon that also after I got it working with Count(1).  I may go back and change it given time if it executes faster.

So all is good.
0
 
awking00Commented:
>> I may go back and change it given time if it executes faster.<<
You might try running execution plans on both to compare likely performance.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Here is a possible third alternative...See if it does better performance wise...

select AA.ServiceID,
AA.TransactionID as ClaimTransactionID,
(AA.TransactionID + AA.diff)  as PaymentTransactionID
from
(
select A.ServiceID, 
A.TransactionID, 
min (B.TransactionID-A.TransactionID) as diff
(
select ServiceID, TransactionID
from Transactions  
where TransactionType='Claim'
)  A
inner join (
select ServiceID, TransactionID
from Transactions  
where TransactionType='Payment'
)  B 
on A.ServiceID=B.ServiceID and A.TransactionID<B.TransactionID 
group by A.ServiceID, A.TransactionID
) AA
order by AA.ServiceID, AA.TransactionID

Open in new window


Thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.