INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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
 

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:
3862074 635444419   1001              3          1001        1  10000081311437527892
3862075 25555596    1001              1          1001        1  10000081211437527803
3862097 63255529    1001              1          1001        1  10000081721437528139
 

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

Dexter

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

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!

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

(OP)
Hi! for reference:
Found out the answer for this one using sub-query to validate

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close