Menu
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?
615
We can use the group by multiple column technique to group multiple records into a single record. All the records that have the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple column technique.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.
Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause. Consider the following example in which we have used DISTINCT clause in first query and GROUP BY clause in the second query, on 'fname' and 'Lname' columns of the table named 'testing'.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
