Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 4 of 4 total |
Macros or Intermediate or virtual columns |
Fri, May 21 2010 7:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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. timyoung@elevatesoft.com | 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 Team Elevate | 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. timyoung@elevatesoft.com | 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 Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |