Great Mike. There might be tweaks and problems for the above, but to move onto the prefiltering... The objective is keep the records for a given account only if they include records for all available years. In other words (probably clearer), to remove the records for any account that does not have records for all years. First list all available years. (Already mentioned above.) SELECT DISTINCT [TAX YEAR] FROM [Res Value by Year] ORDER BY [TAX YEAR] ASC ; This lists (2010, 2011, 2013, 2015, 2017). So records are available for 5 years. We can count that total number directly, by wrapping the above: SELECT COUNT(*) AS [n years] FROM ( SELECT DISTINCT [TAX YEAR] FROM [Res Value by Year] --ORDER BY [TAX YEAR] ASC ) ; The ORDER BY clause would make no difference here so it is commented out. [Note that in Manifold 8 we could count distinct values without using a wrap. SELECT COUNT(DISTINCT [TAX YEAR]) AS [n years] FROM [Res Value by Year] ; We can't do the same in Manifold 9. I assume that is intentional--there will probably be a very good reason. I hadn't noticed this before but should should have. ] So that is the total number of years for the whole table, combining all accounts together: 5. Now for each account, if it has records available for 5 years, it is in. Is it has records available for fewer than 5 years, it is out. (It can't have records for more than 5 years, or the count for the whole table would also be more than 5.) So now we need to know the number of years of data available for each account, taken separately. In Manifold 8 we could have done this: SELECT COUNT(DISTINCT [TAX YEAR]) as [n years] FROM [Res Value by Year] GROUP BY [ACCOUNTNO] ; In Manifold 9 we need* to use a wrap. [*Or see the alternative below.] SELECT [ACCOUNTNO], COUNT([TAX YEAR]) AS [n years] -- not COUNT(*) FROM ( SELECT DISTINCT [ACCOUNTNO], [TAX YEAR] FROM [Res Value by Year] ) GROUP BY [ACCOUNTNO] ; [*An alternative is to use COLLECT, passing the result to a custom aggregate function: FUNCTION CountRecords(t TABLE) FLOAT64 AS (SELECT COUNT(*) FROM t) END ; SELECT [ACCOUNTNO], CountRecords( (COLLECT DISTINCT [TAX YEAR]) ) AS [n years] FROM [Res Value by Year] GROUP BY [ACCOUNTNO] ; ] So now we have two results: the number of available years for all accounts taken together, and the number of available years for each record taken separately. We want to exclude any records where the second number is less than the first.
|