Find the first AND last date that an ID occured in dataset SQL

I have a very large data set with over 1 million rows and over 70 columns. The dataset contains data about back orders of an organization. Every day a snapshot is being made and all orders that are backorders are added to the dataset. I want to know what the first date is when an OrderID occurs in the dataset, and when the last date is when the OrderID occurs. So I will know when the order has been placed as an backorder and when it was removed as backorder. To make my problem more clear I will explain it by giving an example. In the table below I have the column with OrderID’s and a column with the SnapShotDate.

+---------+--------------+
| OrderID | SnapShotDate |
+---------+--------------+
|    1234 | 01/01/2020   |
|    1111 | 01/01/2020   |
|    1122 | 01/01/2020   |
|    1234 | 01/02/2020   |
|    1111 | 01/02/2020   |
|    1234 | 01/03/2020   |
|    1234 | 01/04/2020   |
+---------+--------------+

Does anyone have an idea of how I can get the following result in a view?:

+---------+-------------------+------------------+
| OrderID | FirstDateOccuring | LastDateOccuring |
+---------+-------------------+------------------+
|    1234 | 01/01/2020        | 01/04/2020       |
|    1111 | 01/01/2020        | 01/02/2020       |
|    1122 | 01/01/2020        | 01/01/2020       |
+---------+-------------------+------------------+

I just dont know where to start. I basically have to check when the orderID occurs for the first time in the dataset and when the orderID occurs the last time in the dataset.

If you have any solution or infomration to guide me in the right direction I would be very thankful. Also let me know if there is something not clear in my description of my problem.

Answer

Simply do a GROUP BY. Use MIN() and MAX() to get first and last date.

select OrderID,
       MIN(SnapShotDate) as FirstDateOccuring,
       MAX(SnapShotDate) as LastDateOccuring 
from tablename
group by OrderID

Advertisement

Answer