• Welcome to SQLitening Support Forum. Please login.
 
December 03, 2021, 07:12:10 PM

News:

Welcome to the SQLitening support forums!


Need help with a query...

Started by Marc Giao, April 09, 2011, 02:52:15 AM

Previous topic - Next topic

Marc Giao

Hi Guys,

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 :)

Marc

Fred Meier

What does this return?Select call from tbl_calls where link=0 and status='new' and user<>'supp'

Marc Giao

April 09, 2011, 04:12:20 PM #2 Last Edit: April 09, 2011, 04:14:35 PM by Marc Giao
Hi Fred,

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'


Thanks,

Marc

Fred Meier

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
loop


Marc Giao

Hi Fred,

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.

Thanks,

Marc

Sean Roe

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.

Marc Giao

April 13, 2011, 04:58:56 PM #6 Last Edit: April 13, 2011, 05:10:38 PM by Marc Giao
Hi Sean,

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,

Marc

Sean Roe

April 13, 2011, 07:07:49 PM #7 Last Edit: April 13, 2011, 07:16:55 PM by Sean Roe
Hi Marc,

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.

Thanks,
Sean

P.S. OK i re-read the problem and I see where I went wrong. Let me try again.

Sean Roe

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.

Thanks

Marc Giao

Hi Sean,

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,

Marc

Sean Roe

Thanks Marc,

Good then that is what my second statement is trying to do.

Marc Giao

April 14, 2011, 03:27:47 PM #11 Last Edit: April 14, 2011, 03:30:56 PM by Marc Giao
Hi Sean,

Well, I

Sean Roe

 ;D ROFL

Thanks Marc for the many kind words.