; 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 TSeems to give me what I need.
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] GOAgreements are entered for different Branches (Agreements.agr_brnfk = Branches.brn_pk)
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), GONow 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'), GOMy difficulty is getting the batch numbers generated in order by date.