SQL Query with group by for multiple date ranges

Question

I need to formulate a t-sql query and so far I have been unable to do so. The table that I need to query is called Operations with two columns ,an FK OperationTypeID and an OperationDate. The query needs to return a result which comprises of the count of operation type id during the range specified.

Through the application interface the user can specify multiple operationtype Ids as well as their individual date ranges so for instance, the operationtype id 'A' can be looked for in the range 22/04/2010 to 22/04/2012 and operationtype Id 'B' can be searched in 15/10/2012 to 15/11/2013 and so on for other operation type ids. Now I need to return a count for each operationtype id during each of the range specified for individual operation type Ids.

What is the most efficient way to achieve this in a single t-sql query considering the performance issues ... a rough layout presented below, i am not very good at formatting so i hope it will still give an idea.

+---------------+----------+----------+-----+
|OperationTypeID|Min date  |Max Date  |Count|
+---------------+----------+----------+-----+
|A              |22/04/2010|22/04/2012|899  |
+---------------+----------+----------+-----+
|B              |15/10/2012|15/11/2013|789  |
+---------------+----------+----------+-----+

.... and so on

Would appreciate if anyone can help. The query needs to return a count for each operationtype id based on the min/max date range specified by the user. The Min/Max functions available in sql server probably don't apply here. One possible approach that I have thought of so far makes use of the Union All approach, where I formulate a single query for a single operation type id based on the date range and then do a UNION All, any performance impacts?

Answer 1

You will need to store the search criteria somewhere. The best place, would probably be a temporary table with the following columns:

CREATE TABLE #SearchCriteria (
    OperationTypeId VARCHAR(1)
    MinDate DATETIME
    MaxDate DATETIME
)

Now, once you have populated this table, a simple query like this, should give you what you want:

SELECT OperationTypeId, 
    MinDate, 
    MaxDate,
    (SELECT COUNT(*) FROM Operations 
      WHERE OperationDate BETWEEN SC.MinDate AND SC.MaxDate 
        AND OperationTypeId = SC.OperationTypeId) AS [Count]
FROM
    #SearchCriteria SC

If you must have everything in a single query (without using a temporary table), do something like this:

SELECT OperationTypeId, 
    MinDate, 
    MaxDate,
    (SELECT COUNT(*) FROM Operations 
      WHERE OperationDate BETWEEN SC.MinDate AND SC.MaxDate 
        AND OperationTypeId = SC.OperationTypeId) AS [Count]
FROM
    (VALUES ('A', '22/04/2010', '22/04/2012')
           ,('B', '15/10/2012', '15/11/2013')
         /* ... etc ... */
    ) SC(OperationTypeId, MinDate, MaxDate)