gistfile1.sql

SELECT
	node.`type`,
	node.`sticky` AS `pinned`,
	node.`promote` AND NOT ISNULL(`field_homepage_image_data`) AS `front_page`,
	node_revisions.`title`,
	node_revisions.`teaser`,
	content_field_homepage_image.`field_homepage_image_data` AS `homepage_image`,
	content_field_level_1_image.`field_level_1_image_data` AS `level_1_image`,
	content_field_small_image.`field_small_image_data` AS `small_image`,
	CONCAT('/', url_alias.`dst`) AS `url`,
	(
		CASE node.`type` WHEN 'vuevents' THEN
			GREATEST(
				0,
				LEAST(
					60 * 60 * 24 * 365,
					60 * 60 * 24 * 365 - (UNIX_TIMESTAMP(GREATEST(DATE(NOW()), `field_date_value`)) - UNIX_TIMESTAMP(DATE(NOW())))
				)
			) / (60 * 60 * 24 * 365) * 100
		ELSE
			GREATEST(
				0,
				LEAST(
					60 * 60 * 24 * 365,
					60 * 60 * 24 * 365 - (UNIX_TIMESTAMP(DATE(NOW())) - `created`)
				)
			) / (60 * 60 * 24 * 365) * 100
		END
	) AS `score`
FROM node
LEFT OUTER JOIN content_field_date ON content_field_date.`vid` = node.`vid`
LEFT OUTER JOIN content_field_homepage_image ON content_field_homepage_image.`vid` = node.`vid`
LEFT OUTER JOIN content_field_level_1_image ON content_field_level_1_image.`vid` = node.`vid`
LEFT OUTER JOIN content_field_small_image ON content_field_small_image.`vid` = node.`vid`
LEFT JOIN url_alias ON CONCAT('node/', node.`nid`) = url_alias.`src`
JOIN node_revisions ON node.`vid` = node_revisions.`vid`
WHERE
	node.`type` IN ('campaign_landing_page', 'success_stories', 'vunews', 'vuevents')
	AND (
		ISNULL(content_field_date.`field_date_value`)
		OR (
			DATE(content_field_date.`field_date_value`) >= DATE(NOW()) OR
			DATE(content_field_date.`field_date_value2`) >= DATE(NOW())
		)
	)
GROUP BY node.`nid`
HAVING `score` > 0
ORDER BY `pinned` DESC, `score` DESC;