Sorry for the horrible title.

I'm sure this must have been asked for already  but just in case

I'd like to reduce this to

SELECT
_ID,
_Name,
_Type,
IF(_Status='D','Dead',IF(_Status='H','Hold',IF(_Status='L','Live','?'))) AS _Status,
_Created,
(SELECT MAX(_Date) FROM (SELECT _Date,_fkProjects FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _LastCallDate,
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned,
(SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult,
(SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete,
IF(
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID)-(SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) >0
THEN
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID)-(SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID)
ELSE 0
) AS _Uncalled
FROM Projects
ORDER BY _ID DESC

to

SELECT
_ID,
_Name,
_Type,
IF(_Status='D','Dead',IF(_Status='H','Hold',IF(_Status='L','Live','?'))) AS _Status,
_Created,
(SELECT MAX(_Date) FROM (SELECT _Date,_fkProjects FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _LastCallDate,
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned,
(SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult,
(SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete,
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID)-(SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS temp
IF(temp > 0 THEN temp ELSE 0) AS _Uncalled
FROM Projects
ORDER BY _ID DESC

1. I find it easier to read
2. More importantly it means if I alter one bit of code I CAN'T forget to alter the other bit

Roy Lambert

Roy,

<< I'm sure this must have been asked for already  but just in case >>

Yes, *you* asked for it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


So I did, but that's in the DBISAM suggestions stack <vbg>

Roy Lambert

Roy,

<< So I did, but that's in the DBISAM suggestions stack <vbg> >>

Nah, it wasn't *that* long ago.

--
Tim Young
Elevate Software
www.elevatesoft.com