Help needed on returning only one result

[Ocula]

The following SQL is for phone billing.

cdr.dst=number dialed.

Country table fields are code=area prefix, landloc=geographical location ("Australia" OR "Australia Mobile" for example) and charge=per minute value.

There is a huge variation of country prefix and in the WHERE clause you can see I need to compare the first 3 digits (e.g. mid(cdr.dst,1,3)) through to the first 9 to determine which country and weather it is to a mobile phone and so apply the correct charge to the call (AND country.landloc Not Like '%Mobile').

It's not picking out exactly that. E.G. Aussie is 0061xxxxxxxxx and Aussie Mobile is 00614xxxxxxxx so if the number dialed is a mobile then as per my WHERE clause the first 4 digits are a match for Aussie AND Aussie Mobile numbers BUT it's not getting exactly WHERE country.landloc Not Like '%Mobile' so I get 2 results.

Appreciate your assistance, TIA


SELECT cdr.calldate, cdr.src, cdr.dst, round(0.5+((cdr.billsec)/60),0) AS duration, date(cdr.calldate) as date, time(cdr.calldate) as time,
EXTRACT(YEAR FROM cdr.calldate) AS Year, EXTRACT(MONTH FROM cdr.calldate) AS Month, EXTRACT(DAY FROM cdr.calldate) as Day, country.LANDLOC AS location,
CASE
WHEN $Month='01' THEN 'Jan'
WHEN $Month='02' THEN 'Feb'
WHEN $Month='03' THEN 'Mar'
WHEN $Month='04' THEN 'Apr'
WHEN $Month='05' THEN 'May'
WHEN $Month='06' THEN 'Jun'
WHEN $Month='07' THEN 'Jul'
WHEN $Month='08' THEN 'Aug'
WHEN $Month='09' THEN 'Sep'
WHEN $Month='10' THEN 'Oct'
WHEN $Month='11' THEN 'Nov'
WHEN $Month='12' THEN 'Dec'
ELSE ''
END AS MonthT,
CASE
WHEN cdr.channel LIKE 'Local/%' THEN 'DVRT'
ELSE 'International'
END AS calltype,
country.charge*(round(0.5+(cdr.billsec)/60)) AS callcost
FROM cdr, country
WHERE EXTRACT(YEAR FROM cdr.calldate)=$Year AND EXTRACT(Month FROM cdr.calldate)=$Month AND accountcode='$Password' AND Concat('0',Mid(cdr.src,3,10))=$Number AND (cdr.dst Not Like 'vm%') And (cdr.dcontext Not Like 'from-internal') And (cdr.dcontext Not Like 'from-did-direct') And Length(cdr.dst)>10 And ((Mid(cdr.dst,1,3)=country.code)Or (Mid(cdr.dst,1,4)=country.code) Or (Mid(cdr.dst,1,5)=country.code) Or (Mid(cdr.dst,1,6)=country.code) Or (Mid(cdr.dst,1,7)=country.code) Or (Mid(cdr.dst,1,9)=country.code)) And (cdr.billsec>0) And cdr.disposition='ANSWERED' and country.LANDLOC Not Like '%Mobile'

 

[dhookom]

Are you sure you are in the correct forum "Microsoft: Access Queries and JET SQL Forum"? What brand of SQL requires you to use a SELECT CASE statement to convert a date to a month abbreviation?

Duane
Hook'D on Access
MS Access MVP

 

[Ocula]

The CASE section for month is a value passed through from a website where the customer can enter the month required and is only for display purposes and can be ignored.

This is MySQL. I have posted in another section but haven't had any useful replies.

Subtlties aside from various incarnations of SQL I believe this is a simple problem yet I can't get my head around it. Sorry if I've posted in the wrong area but looking at other posts and replies it seemed more likely I'd get a result here.

 

[PHV]

Double check the parenthesis in your WHERE clause as you mix AND & OR.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ocula]

I'm not sure it is but I won't rule it out. Thing is I can only have one of the OR'd functions and remove the rest and it still doesn't work right e.g.

If dst=0061xxxxxxx

(Mid(cdr.dst,1,4)=country.code) AND country.landloc Not Like '%Mobile')

Will return

Australia AND Australia Mobile
0061 00614

because the first 4 digits will match...but shouldn't it only return Australia because of the Not Like %Mobile ??

Cheers

 

[PHV]

You may have trailing space ?
what about this ?
AND country.landloc Not Like '%Mobile[!]%[/!]'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ocula]

I'm pretty sure extra spaces are ignored. I have also tried the extra %. It's almost behaving like there are 2 entries for Australia with each of the country codes in the table but there isn't. If this helps, the country table looks like

LANDLOC CODE CHARGE
Australia 0061 0.10
Australia Mobile 00614 0.35

 

[Ocula]

IF anyone is curious I have found a solution. It involves creating a 2nd table for mobile numbers, selecting them all then excluding them from the initial test.