Hi,

< update > I may have this solved:

  ; with T as (select ROW_NUMBER()
        over (partition by agr_brnfk order by agr_brnfk, bat_date) as RN
		,bat_date
		,agr_brnfk
      from (select distinct dagreementdate as bat_date, agr_brnfk
							  from agreements
							  union 
							  select distinct dredeemed, agr_brnfk
							  from agreements
							  where dredeemed is not null
							  ) a)
select RN, bat_date, agr_brnfk
from T

Seems to give me what I need.
< end of update >

I need to retrofit some data to group transactions by batch.
Here's a simplified presentation of the problem:

CREATE TABLE [dbo].[Agreements](
	[iAgreementId] [uniqueidentifier] NOT NULL,
	[DAGREEMENTDATE] [date] NOT NULL,
	[DREDEEMED] [date] NULL,
	[agr_brnfk] [uniqueidentifier] NULL,
	[agr_loan_batfk] [uniqueidentifier] NULL,
	[agr_redeem_batfk] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Agreements] PRIMARY KEY CLUSTERED 
(
	[iAgreementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[Branches](
	[brn_Name] [varchar](30) NOT NULL,
	[usr_fk] [uniqueidentifier] NULL,
	[brn_PK] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Branches] PRIMARY KEY CLUSTERED 
(
	[brn_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Batches](
	[bat_number] [int] NOT NULL,
	[bat_brnfk] [uniqueidentifier] NOT NULL,
	[bat_date] [date] NOT NULL,
	[bat_PK] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Batches] PRIMARY KEY CLUSTERED 
(
	[bat_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [Batches_UQ] UNIQUE NONCLUSTERED 
(
	[bat_number] ASC,
	[bat_date] ASC,
	[bat_brnfk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Batches] ADD  CONSTRAINT [DF_bat_number]  DEFAULT (NEXT VALUE FOR [batchnumber]) FOR [bat_number]
GO

Agreements are entered for different Branches (Agreements.agr_brnfk = Branches.brn_pk)

I am now adding in the Batches table and need to relate the Agreements records to the Batches table based on the dAgreementdate and the dRedeemed. So if an Agreement is made on one date it gets assigned to the Batch for that date using the Agreements.agr_loan_batfk and the redeemed date is used to set the Agreements.agr_redeem_batfk.

This is my starting data:

INSERT INTO [dbo].[Agreements]
           ([iAgreementId]
           ,[DAGREEMENTDATE]
           ,[DREDEEMED]
           ,[agr_brnfk]
           ,[agr_loan_batfk]
           ,[agr_redeem_batfk])
     VALUES
           (NEWID()
           ,'2021-01-02'
           ,NULL
           ,'Branch1' -- just using the branch name for clarification but this is a uniqueidentifier
           ,NULL
           ,NULL),
           (NEWID()
           ,'2021-01-02'
           ,'2021-01-02'
           ,'Branch2'
           ,NULL
           ,NULL),
           (NEWID()
           ,'2021-01-03'
           ,'2021-01-04'
           ,'Branch1'
           ,NULL
           ,NULL),
           (NEWID()
           ,'2021-01-05'
           ,NULL
           ,'Branch2'
           ,NULL
           ,NULL),
           (NEWID()
           ,'2021-01-05'
           ,NULL
           ,'Branch1'
           ,NULL
           ,NULL),

GO

Now I need to generate the data for the Batches table so I get a result like this:

INSERT INTO [dbo].[Batches]
           ([bat_number]
           ,[bat_brnfk]
           ,[bat_date])
     VALUES
           (1
           ,'Branch1'
           ,'2021-01-02'),
           (2
           ,'Branch1'
           ,'2021-01-03'),
           (3
           ,'Branch1'
           ,'2021-01-04'),
           (4
           ,'Branch1'
           ,'2021-01-05'),
           (1
           ,'Branch2'
           ,'2021-01-02'),
           (2
           ,'Branch2'
           ,'2021-01-03'),
           (3
           ,'Branch2'
           ,'2021-01-04'),
           (4
           ,'Branch2'
           ,'2021-01-05'),
GO

My difficulty is getting the batch numbers generated in order by date.