This SQL statement will return a list of plugins that meet the following criteria:
- They are not released
- The are not the released version
- Another plugin with the same type, family, & name is released
SELECT Distinct P.PluginID, CASE P.Type WHEN 0 THEN 'Processes, Contact' WHEN 1 THEN 'Processes, Sales' WHEN 2 THEN 'Scripts, Legacy Basic' WHEN 3 THEN 'Forms, Legacy' WHEN 4 THEN 'Report Profiles' WHEN 5 THEN 'Scripts, SQL' WHEN 8 THEN 'Groups, ACO' WHEN 13 THEN 'Macros' WHEN 14 THEN 'Menus' WHEN 15 THEN 'Toolbars' WHEN 18 THEN 'Images, Bitmap' WHEN 19 THEN 'Reports' WHEN 22 THEN 'Forms, Legacy List View' WHEN 23 THEN 'Groups' WHEN 24 THEN 'Forms, Legacy Support' WHEN 25 THEN 'Templates, Mail Merge' WHEN 26 THEN 'XML Schema' WHEN 27 THEN 'Scripts, VBscript' WHEN 28 THEN 'Forms' WHEN 30 THEN 'Main View' WHEN 31 THEN 'Global Script' ELSE 'Unknown' End Type, P.Family, P.Name FROM Plugin P LEFT JOIN Plugin Child ON Child.BasedOn = P.PluginID INNER JOIN ( SELECT Parent.Type, Parent.Family, Parent.Name FROM Plugin Parent INNER JOIN Plugin Child ON Child.BasedOn = Parent.PluginID ) Released ON Released.Type = P.Type AND Released.Family = P.Family AND Released.Name = P.Name WHERE P.BasedOn IS NULL AND Child.PluginID IS NULL ORDER BY P.Type, P.Family, P.Name
This is awesome. Thanks Timmus!