SQL Query with group by for multiple date ranges

group-by, sql, sql-server, sql-server-2008, t-sql

Solution

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)

Problem

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?

Original source