SQL Server How to get MIN(opened_date) and all Open Records

ef1yzkbh 于 2023-04-19 发布在其他

关注(0)|答案(2)|浏览(83)

I have some data that looks like this:
| Number | CI | PLUGIN | Opened Date | State |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | XYZ | A123 | 1/1/2023 | Closed |
| 2 | XYZ | A123 | 2/1/2023 | Closed |
| 3 | XYZ | A123 | 3/1/2023 | Closed |
| 4 | XYZ | A123 | 4/1/2023 | Open |
| 5 | XYZ | A123 | 5/1/2023 | Open |

I want the result of the query to either give me:

Only the oldest record (record with minimum opened date), and the two Open records:
| Number | CI | PLUGIN | Opened Date | State |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | XYZ | A123 | 1/1/2023 | Closed |
| 4 | XYZ | A123 | 4/1/2023 | Open |
| 5 | XYZ | A123 | 5/1/2023 | Open |
Or only the two open records and a created a column for the minimum opened date:
| Number | CI | PLUGIN | Opened Date | State | MIN Opened Date |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 4 | XYZ | A123 | 4/1/2023 | Open | 1/1/2023 |
| 5 | XYZ | A123 | 5/1/2023 | Open | 1/1/2023 |

I tried to filter on State = Open OR min(opened_at) = opened_at but I get the error that aggregate can't be used in the where clause.

I tried using MAX(number) in the query with MIN(opened_at), but then I only get the record for Number = 5. I also need to have record Number = 4.

I also tried creating two queries, one with only open records and one with MIN(Opened Date) and unioning them, but that just gave me all the records.

Using SSMS 19. SQL Server (Windows SQL Server) Any guidance is appreciated.

sql-server

来源：https://stackoverflow.com/questions/76016237/how-to-get-minopened-date-and-all-open-records

2条答案

按热度按时间

6ioyuze21#

Sounds like you just didn't wrap it in a sub query so the windowed function is available to the where clause:

DECLARE @Table TABLE (Number INT, CI NVARCHAR(3), PLUGIN NVARCHAR(4), OpenedDate DATE, State NVARCHAR(6));

INSERT INTO @Table (Number, CI, PLUGIN, OpenedDate, State) VALUES
(1, 'XYZ', 'A123', '1/1/2023', 'Closed'),
(2, 'XYZ', 'A123', '2/1/2023', 'Closed'),
(3, 'XYZ', 'A123', '3/1/2023', 'Closed'),
(4, 'XYZ', 'A123', '4/1/2023', 'Open'),
(5, 'XYZ', 'A123', '5/1/2023', 'Open');

SELECT a.Number, a.CI, a.PLUGIN, a.OpenedDate, a.State, a.MinOpenDate
  FROM (
        SELECT a.Number, a.CI, a.PLUGIN, a.OpenedDate, a.State, MIN(a.OpenedDate) OVER (PARTITION BY a.CI, a.Plugin ORDER BY a.OpenedDate) AS MinOpenDate
          FROM @Table a
       ) a
 WHERE a.OpenedDate = a.MinOpenDate
    OR a.State = 'Open'

We're doing exactly what you described, but we can now referenced the windowed aggregate column in the where clause.

Just as a side note, providing easily reproduceable DDL/DML make it much easier for folks to answer your question.

赞(0）回复(0）举报 2023-04-19

dxxyhpgq2#

We use window functino min()over() to find the first date and then where to only display state = 'open' . Notice it has to be nested or it will filter out the the min(Opened_Date) .

select *
from
(
select *
      ,first_value(Opened_Date) over(order by Opened_Date) as MIN_Opened_Date
from   t
) t
where State = 'Open'

Number	CI	PLUGIN	Opened_Date	State	MIN_Opened_Date
4	XYZ	A123	2023-04-01	Open	2023-01-01
5	XYZ	A123	2023-05-01	Open	2023-01-01

Fiddle

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server How to get MIN(opened_date) and all Open Records

2条答案

相关问题

热门标签

最新问答