Geek Answers Handbook

SQL Query with a group for multiple date ranges

I need to formulate a t-sql query, and so far I have not been able to do this. The table I need to query is called Operations with two columns, FK OperationTypeID and OperationDate. The query should return a result that includes the number of operation type identifiers in the specified range.

In the application interface, the user can specify several operationtype Ids, as well as their individual date ranges, for example, operationtype id'A' can be found in the range 22/04/2010before 22/04/2012and operationtype id'B' can be searched in 15/10/2012before 15/11/2013, etc. for others operation type ids. Now I need to return a counter for each operation identifier in each of the ranges specified for identifiers of individual types of operations.

What is the most efficient way to achieve this in a single t-sql query, taking into account performance problems ... the rough scheme presented below, I am not very good at formatting, so I hope that it will give an idea anyway.

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

.... etc.

It would be nice if someone could help. The request should return a counter for each type of operation identifier based on the minimum date range specified by the user. The Min / Max functions available in the sql server are probably not used here. One of the possible approaches, which, as I thought, still uses the Union All approach, where I formulate one request for a single type of operation identifier based on a date range, and then execute UNION All, does any performance affect?

+4
1

-. , , :

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

, , , :

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

( ), - :

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)
+2

More articles:


All Articles