How to select maximum date within all rows in SQL Server statement?

Question

I have these tables:

Charges (IdCharges IDENTITY(1,1), PRN Varchar(10), Created (DateTime), SeasonCode Varchar(10));
Sales (IdSales IDENTITY(1,1), PRN Varchar(10));
Bounty (IdBounty IDENTITY(1,1), BountyTran varchar(10));

And this SQL code to insert sample data:

INSERT INTO Charges VALUES ('111','2023-06-01','10.1'); 
INSERT INTO Charges VALUES ('222','2023-06-02','10.2'); 
INSERT INTO Charges VALUES ('333','2023-06-03','10.3');

INSERT INTO SALES VALUES ('111');  
INSERT INTO SALES VALUES ('222');
INSERT INTO SALES VALUES ('333');

INSERT INTO Bounty VALUES ('WWW');
INSERT INTO Bounty VALUES ('DDD');
INSERT INTO Bounty VALUES ('FFF');

I have this stored procedure:

ALTER PROCEDURE [dbo].[spGetCharges]
    @StartDate DATETIME
AS
BEGIN
    SELECT
        K.Bounty AS BountyTran
        'C' AS CODE, 
        LEFT(C.SeasonCode, CHARINDEX('.', C.SeasonCode + '.') - 1) AS SCODE
    FROM 
        dbo.Sales S
    INNER JOIN 
        dbo.Charges C ON S.PRN = C.PRN
    INNER JOIN 
        dbo.Bounty B ON S.IdBounty = B.IdBounty
                     AND C.Created >= @StartDate
END

I want to select maximum C.Created - for example:

If stored procedure is executed with '2023-06-02', then the output should be ('DDD', '10', '2023-06-03'), ('FFF', '10', '2023-06-03')

Try the window function `MAX(C.Created) OVER() AS LatestCreated`. That will return the max Created date over the entire result set. The `OVER()` defines the scope over which `MAX()` is applied. An empty scope means the entire result set. — T N, Jun 23 '23 at 20:04

abolfazl sadeghi · Accepted Answer · 2023-06-23T20:57:33.647

1

You can use Window function

You forgot that I put the IdBounty in the SALES table because the join is done based on it

 ALTER PROCEDURE [dbo].[spGetCharges]
    @StartDate DATETIME
AS
BEGIN

    SELECT
        B.BountyTran AS BountyTran,
        --'C' AS CODE, 
        LEFT(C.SeasonCode, CHARINDEX('.', C.SeasonCode + '.') - 1) AS SCODE
        ,max(Created) OVER() AS LatestCreated
    FROM 
        dbo.Sales S
    INNER JOIN 
        dbo.Charges C ON S.PRN = C.PRN
    INNER JOIN 
        dbo.Bounty B ON S.IdBounty = B.IdBounty
                     AND C.Created >= @StartDate

Demo

edited Jun 23 '23 at 20:57

answered Jun 23 '23 at 20:44

abolfazl sadeghi

2,277
2
12
20

1

You can drop the `order by (select 0)` content of the `OVER()` clause, since order is not needed for the `MAX()` function. – T N Jun 23 '23 at 20:54
1

@TN, Thank you For your point,I am used to to use order by – abolfazl sadeghi Jun 23 '23 at 20:58

How to select maximum date within all rows in SQL Server statement?

1 Answers1