Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL SERVER: Query to get the last instance of an ID but return the first instance of that ID

Status
Not open for further replies.

dexterdg

Programmer
Joined
Jan 6, 2013
Messages
85
Location
PH
[pre]
callid calling_pty dialed_num disposition dispvdn segment ucid
3862073 25555366 1001 1 1001 1 10000080561437527378
3862073 291113 1015 2 1015 2 10000080621437527416
3862074 635444419 1001 3 1001 1 10000081311437527892
3862075 25555596 1001 1 1001 1 10000081211437527803
3862075 26669 1014 3 1014 2 10000081231437527820
3862076 23044442 1009 2 1009 1 10000080961437527654
3862079 632444419 1001 1 1001 1 10000081361437527904
3862089 0111808 1001 1 1001 1 10000081671437528068
3862091 02366674 1001 1 1001 1 10000081011437527679
3862091 26666 1015 2 1015 2 10000081091437527712
3862097 63255529 1001 1 1001 1 10000081721437528139
3862097 29111 1014 3 1014 2 10000081771437528168
[/pre]

The table above I are logs of calls.
I need to get the specific record and display it with the following conditions:
1. Must be disposition = 3 [basis]
2. If the record has segment = 3 or segment = 2, the record to be displayed is the one with segment = 1, Hence if none, then display the current record set.
3. Records are grouped by called as the id for the transaction.

Now my dilemma is how can I validate my record set while still on the process of fetching data as stated on the requirement #2.
I tried nested Select and Joins but cant come up to the code to check if that record with disposition = 3 has a previous segment(2 or 1). just after that I can come up with the list.

With the above sample the result should be:
[pre]
3862074 635444419 1001 3 1001 1 10000081311437527892
3862075 25555596 1001 1 1001 1 10000081211437527803
3862097 63255529 1001 1 1001 1 10000081721437528139
[/pre]

Im pretty much stuck right now on my development because of this.
Thanks in advance!

Dexter
 
Have you tried an EXISTS? WHERE EXISTS (SELECT x FROM table1 WHERE segment IN (3, 2) AND ...)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Hi! for reference:
Found out the answer for this one using sub-query to validate

SQL:
SELECT callid, 
       (SELECT calling_pty 
        FROM Log l
        WHERE Log.callid = l.callid AND
              segment = (SELECT MIN(segment) 
                         FROM Log l2
                         WHERE l.callid = l2.callid) 
       )[calling_pty], 
       dialed_num, disposition, dispvdn, segment, ucid
FROM Log
WHERE disposition = 3

courtesy of:
PM 77-1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top