SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #MemberTypes(
    [Id]          INT           NOT NULL identity(1,1),
    [MemberType]  NVARCHAR (10) NOT NULL,
)

CREATE TABLE #ChargeTypes(
    [Id]            INT           IDENTITY (1, 1) NOT NULL,
    [ChargeType]    NVARCHAR (45) NOT NULL,
    [isAGroupRate]  bit             not null
)

CREATE TABLE #MemberTypeChargeType(
    MemberTypeId    int not null
    ,ChargeTypeId   int not null, 
    CONSTRAINT [PK_#MemberTypeChargeType] PRIMARY KEY ([MemberTypeId], [ChargeTypeId])
)

CREATE TABLE #ChargeRates(
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [ChargeTypeId] INT            NOT NULL,
    [EarlyBird]    BIT            DEFAULT ((0)) NOT NULL,
    [FromCount]    SMALLINT       NOT NULL,
    [ToCount]      SMALLINT       NOT NULL,
    [RatePrice]    DECIMAL (6, 2) NOT NULL,
    CONSTRAINT [PK_#ChargeRate_Id] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UX_#ChargeType_EarlyBird_FromCount] UNIQUE NONCLUSTERED ([ChargeTypeId] ASC, [EarlyBird] ASC, [FromCount] ASC)
);

insert into #MemberTypes(MemberType)
    values('Regular'), ('Doctor')
    
insert into #ChargeTypes(ChargeType, [isAGroupRate])
    values('Regular',0), ('Service People',0), ('Reciprocation',1)

insert into #MemberTypeChargeType(MemberTypeId, ChargeTypeId)
    values(2,2), (1,1), (1,3)
    
insert into #ChargeRates([ChargeTypeId],[EarlyBird],[FromCount],[ToCount],[RatePrice])
    values(3,1,10,25,65.00),
        (3,0,10,25,75.00),
        (3,1,26,50,35.00),
        (3,0,26,50,45.00),
        (3,1,51,9999,0.00),
        (3,0,51,9999,0.00),
        (1,1,1,10,6.00),
        (1,1,11,9999,3.00),
        (1,0,1,10,7.00),
        (1,0,11,9999,4.00),
        (2,1,1,9999,8.00),
        (2,0,1,9999,8.00)
        
--I then have another table 
CREATE TABLE #OrderItems (
    id              int     identity(1,1),
    [OrderId]       int        NOT NULL,
    RecipientId      INT           NOT NULL,
    Reciprocated    bit         not null,
    MemberTypeId    INT         NOT NULL,
    CONSTRAINT [PK_#OrderItem] PRIMARY KEY ([OrderId], RecipientId)
);

--with data similar to this

insert into #OrderItems(OrderId, RecipientId, Reciprocated, MemberTypeId)
    values(129,4,0,1),
        (129,8,0,1),
        (129,13,0,1),
        (129,23,0,1),
        (129,30,0,1),
        (129,31,0,1),
        (129,39,0,1),
        (129,41,0,1),
        (129,42,0,1),
        (129,45,0,1),
        (129,55,0,1),
        (129,59,0,1),
        (129,60,1,1),
        (129,71,0,1),
        (129,72,0,1),
        (129,73,0,1),
        (129,77,0,1),
        (129,87,0,1),
        (129,92,0,1),
        (129,96,0,1),
        (129,100,0,1),
        (129,110,0,1),
        (129,111,0,1),
        (129,120,0,1),
        (129,123,0,1),
        (129,129,0,1),
        (129,134,0,1),
        (129,137,1,1),
        (129,139,0,1),
        (129,142,1,1),
        (129,153,0,1),
        (129,158,0,1),
        (129,163,0,1),
        (129,170,0,1),
        (129,173,0,1),
        (129,178,1,1),
        (129,186,0,1),
        (129,195,0,1),
        (129,208,0,1),
        (129,216,0,1),
        (129,217,0,1),
        (129,223,0,1),
        (129,229,0,1),
        (129,236,0,1),
        (129,244,0,1),
        (129,256,0,1),
        (129,270,0,1),
        (129,272,1,1),
        (129,282,0,1),
        (129,298,0,1),
        (129,311,0,1),
        (129,321,0,1),
        (129,323,0,1),
        (129,327,0,1),
        (129,329,0,1),
        (129,330,0,1),
        (129,344,1,1),
        (129,366,0,2),
        (129,368,0,1),
        (129,372,0,1),
        (129,410,0,1),
        (129,412,0,1),
        (129,431,0,1),
        (129,451,0,1),
        (129,546,0,1),
        (129,552,0,1),
        (129,612,1,1),
        (129,741,0,1),
        (129,748,0,2),
        (129,749,1,1),
        (129,750,1,1),
        (129,756,1,1),
        (129,778,0,1),
        (129,781,1,1),
        (129,810,0,1),
        (129,822,1,1),
        (129,867,0,1),
        (129,873,0,1),
        (129,901,0,1),
        (129,955,0,2),
        (129,983,0,1),
        (129,1034,1,1),
        (129,1060,0,1)

103 rows affected

with cte
as
(
  select mtct.ChargeTypeId, 
         TotalCount = count(*)
  from   #OrderItems oi
         inner join #MemberTypeChargeType mtct on mtct.MemberTypeId = oi.MemberTypeId
  where Reciprocated  = 0
  group by mtct.ChargeTypeId
),
cte1 as
( -- ChargeType : 1, 2
  select c.ChargeTypeId, c.TotalCount, cr.Id, cr.FromCount, cr.ToCount, cr.RatePrice,
         i.ItemCount, 
         Total = cr.RatePrice * i.ItemCount
  from   cte c
         inner join #ChargeRates cr on  cr.ChargeTypeId = c.ChargeTypeId
                                    and cr.FromCount   <= c.TotalCount
         cross apply
         (
             select ItemCount = case when c.TotalCount >= cr.ToCount
                                     then cr.ToCount - cr.FromCount + 1
                                     else c.TotalCount - cr.FromCount + 1
                                     end
       ) i
  where  cr.EarlyBird = 1
  and    c.ChargeTypeId <> 3
),
cte2 as
( -- ChargeType : 3
  select c.ChargeTypeId, c.TotalCount, cr.Id, cr.FromCount, cr.ToCount, cr.RatePrice,
        i.ItemCount, 
        Total = cr.RatePrice * i.ItemCount
  from   cte c
         inner join #ChargeRates cr on  cr.ChargeTypeId = c.ChargeTypeId
                                    and cr.FromCount   <= c.TotalCount
                                    and cr.Tocount     >= c.TotalCount
         cross apply
         (
           select ItemCount = case when c.TotalCount >= cr.ToCount
                                   then cr.ToCount - cr.FromCount + 1
                                   else c.TotalCount - cr.FromCount + 1
                                   end
        ) i  
  where  cr.EarlyBird = 1
  and    c.ChargeTypeId = 3
)
select *
from   cte1
union all
select *
from   cte2
order by ChargeTypeId, FromCount

ChargeTypeId	TotalCount	Id	FromCount	ToCount	RatePrice	ItemCount	Total
1	67	7	1	10	6.00	10	60.00
1	67	8	11	9999	3.00	57	171.00
2	3	11	1	9999	8.00	3	24.00
3	67	5	51	9999	0.00	17	0.00