Just in case somebody has a similar problem… Coming mainly from MySql I had to deal with some MsSql/SQL Server specialties. Consider the following working SQL for MsSql:
SELECT AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM FROM AKTIONEN AS AK WITH (nolock) LEFT JOIN BUDGET_TRAEGER AS BU ON AK.AK_BUDGET_NUMMER=BU.BUDGET_NUMMER LEFT JOIN AKTIONSTYP AS TY ON AK.AK_TYP=TY.AKTIONSTYP_TYP LEFT JOIN AKTIONEN_FILIALE AS AF ON AK.AK_LFDNR=AF.AF_LFDNR WHERE ( ( AK.AK_ENDEDATUM<=CAST('2010-06-30' AS smalldatetime) AND AK.AK_ENDEDATUM>=CAST('2010-01-01' AS smalldatetime) ) OR ( AK.AK_STARTDATUM<=CAST('2010-06-30' AS smalldatetime) AND AK.AK_STARTDATUM>=CAST('2010-01-01' AS smalldatetime) ) ) GROUP BY AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM;
Interesting parts explained:
(a) The addition ‘WITH (nolock)’ prevents my application from locking the table during select queries.
(b) The fields from which I liked to select a bunch of records touching a daterange between AK_STARTDATUM and AK_ENDEDATUM were of the fieldtype ‘smalldatetime’. This is some strage SQL Server format. It needs to be converted to with the CAST() function in order to return the expected resultsets. It understands several input formats. You can test the correct conversion with a simplified SQL statement like this: SELECT CAST(’2010-03-29′ AS smalldatetime);
(c) The statement will not work and return an error if you do not include ALL your selected fields also in the GROUP BY clause.