CODEWITHSUNDEEP
sqlsql-server
ChunkyFresh
ChunkyFresh

Reputation: 65

SQL Server: Query to Group By and Have Date Minimum and Maximum in Result

I was wondering if someone could please help me out with a query I'm trying to write.I'm operating on SQL Server syntax.

I'm trying to group these rows by Group and have the minimum date and the maximum date associated to that group also show up in my output.

I'm sorry for not having code to offer. I just really wasn't able to come up with anything. This one went beyond my understanding of SQL. Wouldn't mind if explanation or theory is added to solution as well for learning.

Again thanks to anyone in advance who can help me.

Here's the example.

Data Sample:

+----------------+-------------+-------------------+-----------------+ 
|      Name      |   Group     |    CheckInDate    |   CheckOutDate  |
+----------------+-------------+-------------------+-----------------+
|      Rogue     |   Group 1   |     01/03/2019    |   01/08/2019    |
+----------------+-------------+-------------------+-----------------+ 
|      Larry     |   Group 3   |     01/01/2019    |   01/07/2019    |
+----------------+-------------+-------------------+-----------------+
|      Jorge     |   Group 2   |     01/02/2019    |   01/04/2019    |
+----------------+-------------+-------------------+-----------------+ 
|      Tara      |   Group 1   |     01/02/2019    |   01/07/2019    |
+----------------+-------------+-------------------+-----------------+ 
|      Luca      |   Group 2   |     01/03/2019    |   01/08/2019    |
+----------------+-------------+-------------------+-----------------+

Query Output 

 +----------------+-----------------+-------------------+-----------------+
 |   Description  |      Count      |    CheckInDate    |   CheckOutDate  | 
 +----------------+-----------------+-------------------+-----------------+ 
 |     Group 1    |        2        |     01/02/2019    |    01/08/2019   |     
 +----------------+-----------------+-------------------+-----------------+
 |     Group 2    |        2        |     01/02/2019    |    01/08/2019   | 
 +----------------+-----------------+-------------------+-----------------+
 |     Group 3    |        1        |     01/01/2019    |    01/07/2019   |
 +----------------+-----------------+-------------------+-----------------+

Upvotes: 0

Views: 43

Answers (1)

Yang
Yang

Reputation: 8170

You need to group by the Group, and do a min and max on the date columns:

SELECT
  Group AS Description, COUNT(*) AS Count,
  MIN(CheckInDate) AS CheckInDate,
  MAX(CheckOutDate) AS CheckOutDate
FROM Dates
GROUP BY Group;

Upvotes: 1

Related Questions