Chris
-----------------------------------------------------------------------------------------------------------------------------------
select  `lpa` ,"@year start - yyyy-mm-dd@" `start`,"@year end - yyyy-mm-dd@" `end` ,
(select count(`lpa`) from her_devconsult
  where `lpa`=`dc`.`lpa` and compiledon between `start` and `end`  
) `Number of consultations`,
(select count(`lpa`) from her_devthreat
  where `lpa`=`dc`.`lpa` and catagory='planning' and compiledon between `start` and `end`  
) `Number logged into THREAT that might require further action`,
(select count(`lpa`) from her_devthreat
  where `lpa`=`dc`.`lpa` and catagory='planning' and predet6<>'no further action' and compiledon between `start` and `end`  
) `Number where CPAT made recommendations`
from her_devconsult `dc`
group by `lpa`
having `Number of consultations`>0 and length(trim(`lpa`))>0

Gary
-----------------------------------------------------------------------------------------------------------------------------------
select `her_event`.`watprn` `prn`,
`her_source1`.`url`,
if(length(trim(her_source1_link.url))>5,
concat('https://walesher1974.org/herumd.php?group=CPAT&level=2&docid=',301298618+`her_source1_link`.`rowid`),
concat('https://walesher1974.org/herumd.php?group=CPAT&level=3&docid=',301298618+`her_source1`.`rowid`)
) `url`,
(select substring_index(group_concat(concat('https://walesher1974.org/her/groups/cpat/photo_archive/',her_photo_meta.filename) order by publish desc),',',5)
from her_photo_link
join her_photo_meta on her_photo_meta.photonum=her_photo_link.photonum
where her_photo_link.watprn=`her_event`.`watprn` and her_photo_meta.restricted=0 and her_photo_meta.file_exists>0
) `images`
from `her_event`
join her_source1_link on her_source1_link.watprn=her_event.watprn
join her_source1 on her_source1.rowid=her_source1_link.source_id
where locate('.pdf',if(length(trim(her_source1_link.url))>5,her_source1_link.url,her_source1.url))>0
group by `her_event`.`prn`

`her_event`.`prn`=166908