找到最小值和最大值及其位置

问：

USE [college]
GO
/****** Object:  StoredProcedure [dbo].[Customer]    Script Date: 11/16/2023 10:35:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Customer] @date int, @id int
as
begin
WITH RankedCustomerData AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerData.PersonId ORDER BY CustomerData.Ammount DESC) AS max_row_num,
        ROW_NUMBER() OVER (PARTITION BY CustomerData.PersonId ORDER BY CustomerData.Ammount ASC) AS min_row_num,
         CustomerData.id,
         PersonData.[Name],
        CustomerData.PersonId,
        CustomerData.[Date],
        CustomerData.Ammount
    
    FROM
        CustomerData INNER JOIN PersonData ON CustomerData.PersonId = @id and PersonData.id = @id
   AND
        Date = @date
)
SELECT
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Name END) AS [User],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Date END) AS Date,
    MIN(CASE WHEN min_row_num = 1 THEN RankedCustomerData.Ammount END) AS [Min amount],
      MIN(CASE WHEN min_row_num = 1 THEN RankedCustomerData.id END) AS [Min position],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Ammount END) AS [Max amount],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.id END) AS [Max position]
FROM
    RankedCustomerData;

    end;

我想要最小和最大金额以及它们相对于 Date 和 PersonId 的位置

我试试这个查询

USE [college]
GO
/****** Object:  StoredProcedure [dbo].[Customer]    Script Date: 11/16/2023 10:35:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Customer] @date int, @id int
as
begin
WITH RankedCustomerData AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerData.PersonId ORDER BY CustomerData.Ammount DESC) AS max_row_num,
        ROW_NUMBER() OVER (PARTITION BY CustomerData.PersonId ORDER BY CustomerData.Ammount ASC) AS min_row_num,
         CustomerData.id,
         PersonData.[Name],
        CustomerData.PersonId,
        CustomerData.[Date],
        CustomerData.Ammount
    
    FROM
        CustomerData INNER JOIN PersonData ON CustomerData.PersonId = @id and PersonData.id = @id
   AND
        Date = @date
)
SELECT
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Name END) AS [User],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Date END) AS Date,
    MIN(CASE WHEN min_row_num = 1 THEN RankedCustomerData.Ammount END) AS [Min amount],
      MIN(CASE WHEN min_row_num = 1 THEN RankedCustomerData.id END) AS [Min position],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.Ammount END) AS [Max amount],
    MAX(CASE WHEN max_row_num = 1 THEN RankedCustomerData.id END) AS [Max position]
FROM
    RankedCustomerData;

    end;

    EXEC Customer @id=3, @date = 20231115
but query return this
User=Hisham, Date=20231115,Min amount=280, Min position=16,Max amount=320, Min amount=15
in actual table
id  PersonId    Amount  Date
1   1   100 20231106
2   1   50  20231106
3   1   70  20231106
4   1   20  20231106
5   1   30  20231106
6   1   110 20231106
7   1   40  20231106
8   1   20  20231113
9   2   200 20231115
10  2   210 20231115
11  2   220 20231115
12  2   205 20231115
13  3   300 20231115
14  3   310 20231115
15  3   320 20231115
16  3   280 20231115

最小位置是 4，最大位置是 3，我想要相对于日期的结果位置