I need help one again, my knowledge of SQL is still very limited and I've been fighting this one for a wile :-[
Assume the following table, tbl_calls:
call link status user
1752 0 new mike
1752 1 active supp
1752 2 active mike
1752 3 new supp
1475 0 new mike
1558 0 new jeff
1558 1 active supp
1558 2 new mike
1299 0 new tony
1299 1 active mike
1299 2 new jeff
-call/link combination is always unique
-status for the first link (0) is always the same as the status for the last (highest) link
-user could be any name
What I need is query that will return the "call" number of every call whose last "link" is "new" but does not equal "supp".
For the above table example the numbers 1558, 1475 and 1299 should be returned.
My efforts always seem to trip over call 1475, the only solution that works for me is to first do a query where link=0 and status=new and user<>supp then in my code finding the last "link" and checking if status=new and user<>supp. However, I'd like to do it using an SQL query, I think it's possible but so far, no luck.
Thanks for any and all help :)
What does this return?
Select call from tbl_calls where link=0 and status='new' and user<>'supp'
It returns all of then because, in this case, all "link 0" records are "new" and don't equal "supp". I need the same thing but for the last link, the link with the largest values for each call.
The following almost gets what I need but, not surprising, misses call number 1475
Select call from tbl_calls
where call in
(Select call from tbl_calls where link=0 and status='new')
and link<>0 and status='new' and user<>'supp'
I don't know of a way using only SQL, but there might be. I would probable use something like this. (Not tested)
Local lsPrevCall as String
Select call, user from tbl_calls where statu='new' order by call, link desc
do while slGetRow
if lsPrevCall = slFN("call") then iterate do
lsPrevCall = slFN("call")
if slFN("user") = "supp" then iterate do
' lsPrevCall should now have the highest link for this call that is new and not supp
Yep, that's pretty much I resolved it too but I was hoping for a more elegant solution. If I find a purely SQL solution I'll report back.
Try this one:
select call,max(link),status,user from tbl_calls where status = 'new' and user <> 'supp'
group by call
order by rowid
I only included the order by so that it would be in the same order you had in your problem.
No "bragging rights" yet :)
An image is better than any description of mine, taken from real data on the live database. Aside from changing the column names to work with the live database, the only other thing I added was "DESC" to your "ORDER BY" statement. Yellows are correct, reds show where the query failed (they should not show):
All the best,
My understanding of the problem is that for each 'call' you wanted the largest value 'link' that was associated with the status = new and not supp. Could you tell me again what it should be because I am not sure what the difference between the yellow and red on the left picture. The one on the right has all red for supp while the one on the right has red for all but supp.
P.S. OK i re-read the problem and I see where I went wrong. Let me try again.
Ok, try this one
select * from (
select call,max(link),status,user from tbl_calls where
status = 'new'
group by call) where user <> 'supp'
Let me know how it goes.
I should have explained better.
The image on the right is the table as it exists in the live database showing the calls sorted in descending order. The yellow rows are the calls numbers the query should return, in this case 1106 and 1105. The red rows are the calls number that should not be returned, in this case 1104 to 1100.
The image on the left is what your query (shown below the image) returns. The yellow rows are the call numbers that should have been and were, correctly returned. The red ones are call numbers that should not have been returned but were.
As can be seen, a call number (call_id) may have any number of links (call_link), the query should be checking the last or highest link for each call number, for the condition "call_link='NEW and call_status<>'SUPPORT'". So, if the call has 1 link, we test link 0, if 2 links we test link 1, if 3 links we test link 2, if 4 links we test link 3, if 5 links we test link 4 etc.
I can't test you latest statement from this pc but will let you know the results as soon as I get to the other laptop.
Thanks for trying,
Good then that is what my second statement is trying to do.
Thanks Marc for the many kind words.