I am having an issue getting the query below to run successfully. I have a similar modified query and it runs fine this one however is returning a syntax error and I can't find the error, and it could be a case of staring at it for too long so I've missed it.
What I am trying to do is isolate new clients added in a particular period where there is no closed box ticked and the open dates is in a certain period. as well as gathering how they became clients. Any advice and assistance you can offer would be appreciated. Thanks in advance.
PARAMETERS [StartDate] DateTime, [End Date] DateTime;
SELECT count(ClientID) AS ClientCount, ReferralType
FROM (SELECT DISTINCT ClientID, ReferralAgent AS ReferralType FROM (SELECT ClientID, ReferralAgent, ClosureDate, CurrentOpenDate, DateFirstOpened FROM TClientInfo WHERE ((((Closuredate) Is Null) AND ((CurrentOpenDate)>= [Startdate] AND <=[End Date])) Or (((Closuredate) Is Null) AND ((DatefirstOpened)<=[End Date])) Or (StartDate <= [ClosureDate] and [ClosureDate] <= [End Date]) OR ((Closuredate)>=[End Date]))) AS C) AS [%$##@_Alias]
GROUP BY ReferralType;
What I am trying to do is isolate new clients added in a particular period where there is no closed box ticked and the open dates is in a certain period. as well as gathering how they became clients. Any advice and assistance you can offer would be appreciated. Thanks in advance.
PARAMETERS [StartDate] DateTime, [End Date] DateTime;
SELECT count(ClientID) AS ClientCount, ReferralType
FROM (SELECT DISTINCT ClientID, ReferralAgent AS ReferralType FROM (SELECT ClientID, ReferralAgent, ClosureDate, CurrentOpenDate, DateFirstOpened FROM TClientInfo WHERE ((((Closuredate) Is Null) AND ((CurrentOpenDate)>= [Startdate] AND <=[End Date])) Or (((Closuredate) Is Null) AND ((DatefirstOpened)<=[End Date])) Or (StartDate <= [ClosureDate] and [ClosureDate] <= [End Date]) OR ((Closuredate)>=[End Date]))) AS C) AS [%$##@_Alias]
GROUP BY ReferralType;