sql to get next id (SQL Server)

What exactly is your question ?  Can you list your requirements exactly so that we can suggest a design.

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.

Please detail the transactions table columns.  Give some sample data and output.

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"

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)

I just noticed that this is in the PHP area.  It should only be in SQL, sorry

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

Select allOpen in new window

Hope this helps...

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).

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.

unfortunately, the system does not have a "junction table".  that's what this query will become.

Did you mean line 8 to read A.ServiceID = B.ServiceID ?

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

Select allOpen in new window

Hope this helps...

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

Select allOpen 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."

OK I see your problem now.  Working on it.

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

Select allOpen in new window

the "could not be bound" error was in my join on statement, not my subquery.

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

Have you tried to the last query ?  How did it go ?

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

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.

>> 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.

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

Select allOpen in new window

Thanks..