![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 4 of 4 total |
![]() |
Fri, May 21 2010 7:56 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, May 21 2010 3:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Sat, May 22 2010 2:53 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
So I did, but that's in the DBISAM suggestions stack <vbg> Roy Lambert |
Tue, May 25 2010 5:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |