annual member query

gistfile1.sql

SELECT DISTINCT
	[CUSTOMER].[ID],
	[CUSTOMER].[CUSTOMDATE1] AS [EXPIRATIONDATE],
	MAX([TRANSACTION].[TIME]) AS [PURCHASEDATE],
	(CASE [ITEM].[ID] WHEN 153 THEN N'child' WHEN 154 THEN N'senior' WHEN 588 THEN N'fitness' WHEN 589 THEN N'fitness' ELSE N'annual' END) AS [MEMBERSHIPTYPE]
FROM
	[CUSTOMER] INNER JOIN [TRANSACTION] ON
	([CUSTOMER].[ID] = [TRANSACTION].[CUSTOMERID]) INNER JOIN [TRANSACTIONENTRY] ON
	([TRANSACTION].[TRANSACTIONNUMBER] = [TRANSACTIONENTRY].[TRANSACTIONNUMBER]) INNER JOIN [ITEM] ON
	([TRANSACTIONENTRY].[ITEMID] = [ITEM].[ID]) 
WHERE
	((([TRANSACTIONENTRY].[ITEMID] IN (157, 152, 153,668, 261, 156, 692, 231, 348, 154, 544, 545, 588, 589, 329, 158, 627, 161, 628, 232, 315, 160, 263, 330)) AND (([TRANSACTION].[TIME] >= {d '2008-10-02'}) AND ([TRANSACTION].[TIME] <= {d '2008-11-01'})))
	AND
([CUSTOMER].[ID] NOT IN 
(
	SELECT [CUSTOMERID] FROM [MEMBERSHIPNOTIFICATIONLOG] WHERE ([MEMBERSHIPTYPE] = N'annual')))
)
GROUP BY
	[Customer].[ID],
	[CUSTOMER].[CUSTOMDATE1],
	[Item].[ID]