How to fetch new records in mssql database using stored procedures

Question

I have a select query which I can join two table to get the data. I would like to run the query every day and return the latest row which got inserted for that day.

Here is the query which i'm using.I'm pretty new to stored procedures. Can anyone help me how can I use that to fetch the new rows.

select [dbo].[Archive1].id,[dbo].[Archive1].loadId,[dbo].[Archive2].costResponse
from [dbo].[Archive1],[dbo].[Archive2]
where [dbo].[Archive1].id = [dbo].[Archive2].transactionNumber;

Answer 1

I was able to write the procedure which works for my use-case

    IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getReport')
    DROP PROCEDURE getReport
    GO
    CREATE PROCEDURE getReport AS
    BEGIN
    DECLARE @nowLocal DATETIMEOFFSET = getDate() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'
    DECLARE @midnightLocal DATETIMEOFFSET = CONVERT(DATETIME2, CAST(@nowLocal as DATE)) AT TIME ZONE 'Central Standard Time'
    DECLARE @dayStart DATETIMEOFFSET = DATEADD(hh, -17, @midnightLocal)
    DECLARE @reportTime DATETIMEOFFSET = DATEADD(hh, 07, @midnightLocal)
    if @nowlocal < @reportTime
        SELECT @dayStart = DATEADD(d, -1, @dayStart)
    DECLARE @dayEnd DATETIMEOFFSET = DATEADD(ms, 86399999, @dayStart)
    select  @dayStart as dayStart, @dayEnd as dayEnd, @reportTime as reportTime, @midnightLocal as midnight
    select [dbo].[Archive1].id,[dbo].[Archive1].loadId,[dbo].[Archive2].costResponse
    from [dbo].[Archive1],[dbo].[Archive2]
    where [dbo].[Archive1].id = [dbo].[Archive2].transactionNumber
    BETWEEN @dayStart AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' and @dayEnd AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC'
    END