Counting the dead!!

[linker3000]

Hi,

 

I am processing some info imported into MySQL 5.0.48 from an ancient (but still in use) system that holds veterinary records. I need to identify whether a client still has any live pets (so we don't contact clients where all their pets are deceased!). The important parts of the animals database are structured as follows:

 

anmalid = Varchar 6

notes = varchar 255

 

The notes need to be checked for various strings such as 'deceased', 'pts' (put to sleep), 'transferred' etc - we have worked out a list of what to check.

 

The animalid represents the owner+the animal in the format xxxxxp, where:

 

'xxxxx' is the client ID - related to the clients list and 'p' is a letter representing the pet number

 

(so presumably with this system a client can only ever have a max of 26 pets in total and there can only be 99999 clients)

 

eg: A client with two pets may have two entries:

 

12345A  fluffy  deceased

and

12345B  rover  dental work completed

 

So, basically, I need to group by xxxxx and count how many animals each customer has and then subtract the ones where the description includes a string that implies the pet is either dead or has been reallocated to another customer etc.

 

Once I have a list of customers and the number of active pets they have I need to extract the email addresses from a separate table - which should be easy with something like:

 

select clientid, emailaddr from clients where livepets >0 

or perhaps..

select clientid, emailaddr from clients where totalpets-deadpets >0

 

I am not sure whether setting up a view first would be useful or whether I have to nest some queries to do the pet counting - any guidance would be appreciated. Yep, I am quite new to SQL 

 

 

Thanks.

[linker3000]

Well, having picked a few brains elsewhere I came up with the following - I would appreciate any comments.

 

SELECT distinct clients.clientid,

    lower(substring(clients.mobemail from 21)) as emailaddr,

    clients.salutation,

    clients.initials,

    clients.surname

  FROM

    VETM0020animals left join clients on (clients.clientid = left(anid,length(anid)-1))

  where not (

  (notes like '%deceased%') or

  (notes like '%write off%') or

  (notes like '%home%') or 

  (notes like '%died%') or

  (notes like '%move%') or

  (notes like '%lost%') or 

  (notes like '%gone%') or

  (notes like '%longer %') or

  (notes like '%bad %') or

  (notes like '%not owned%') /* or

  (notes like '%have%') */

  )

  and clients.mobemail like '%@%'

group by clients.clientid

order by clients.surname

 

I am quickly realising that writing SQL queries requires a different midset than that for writing applications - in my case, my 'programming thinking' made this problem seem much more complex that it really was.

 

Thanks

[fenway]

that NOT IN is very expensive... are you running this often, or just as maintenance once?

[linker3000]

Hi,

 

The query is run perhaps once a month on a copy of the data so no major issues there, but if there's a more elegant way of doing it I would appreciate the feedback in order to improve my SQL skills.

 

Thanks

[fenway]

Yeah, that's not very elegant at all.. but you're limited by the denormalization of the source data... and I assume you don't want to fix it during the import.

 

At the very least, make a fulltext index for the notes and use that.

[Barand]

I'm puzzled by the LEFT JOIN, implying list all animals whether they have a client ot not.

 

If they dont have a client, who are you going to email?

[linker3000]

1) Normalization of data/indexing - not really worth it. The report takes about 8 seconds to process 393K animal records and it's not run that often. This particular app is being replaced soon (< 6 months) and the replacement software, very sensibly, just has a flag field to indicate whether an  animal is alive or not.

 

2) LEFT JOIN: Probably just down to my inexperience of SQL - if you have time to suggest a better way of getting the client data when a live animal has been found then I would appreciate the feedback.

 

Thanks again everyone.

[Barand]

2) LEFT JOIN: Probably just down to my inexperience of SQL - if you have time to suggest a better way of getting the client data when a live animal has been found then I would appreciate the feedback.

 

INNER JOIN instead of LEFT JOIN