Solved

sql to get next id  (SQL Server)

Posted on 2013-06-24
24
474 Views
Last Modified: 2013-06-26
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.
0
Comment
Question by:KCTechNet
  • 12
  • 9
  • 2
  • +1
24 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39272898
What exactly is your question ?  Can you list your requirements exactly so that we can suggest a design.
0
 

Author Comment

by:KCTechNet
ID: 39272949
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39272971
Please detail the transactions table columns.  Give some sample data and output.
0
 

Author Comment

by:KCTechNet
ID: 39272985
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
 

Author Comment

by:KCTechNet
ID: 39273002
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
 

Author Comment

by:KCTechNet
ID: 39273015
I just noticed that this is in the PHP area.  It should only be in SQL, sorry
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39273078
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39273081
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39273096
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
 

Author Comment

by:KCTechNet
ID: 39273116
unfortunately, the system does not have a "junction table".  that's what this query will become.
0
 

Author Comment

by:KCTechNet
ID: 39273120
Did you mean line 8 to read A.ServiceID = B.ServiceID ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39273157
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:KCTechNet
ID: 39273166
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39273174
OK I see your problem now.  Working on it.
0
 

Author Comment

by:KCTechNet
ID: 39273233
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
 

Author Comment

by:KCTechNet
ID: 39273241
the "could not be bound" error was in my join on statement, not my subquery.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 39273256
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
 

Author Comment

by:KCTechNet
ID: 39278301
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39273286
Have you tried to the last query ?  How did it go ?
0
 

Author Comment

by:KCTechNet
ID: 39275303
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
 
LVL 31

Accepted Solution

by:
awking00 earned 400 total points
ID: 39275866
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
 

Author Comment

by:KCTechNet
ID: 39276308
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
 
LVL 31

Expert Comment

by:awking00
ID: 39278071
>> 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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39279763
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now