?
Solved

sql to get next id  (SQL Server)

Posted on 2013-06-24
24
Medium Priority
?
510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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 111

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
 

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 400 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 32

Accepted Solution

by:
awking00 earned 1600 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 32

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

764 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