I think this is best explained by providing my data - It is a bit long.
I have the following tables:
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)
The result I need is this:
| ChargeTypeId | FromCount | ToCount | ItemCount | Price | Total |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 10 | 6.00 | 60 |
| 1 | 11 | 9999 | 57 | 3.00 | 171 |
| 2 | 1 | 9999 | 3 | 8.00 | 24 |
| 3 | 51 | 9999 | 67 | 0.00 | 0 |
In this result, I assume that the person who made the order was an EarlyBird and Chose to Reciprocate.
First, any Item #OrderItems that has Reciprocated = true needs to be ignored
In this case, there are 83 items - 13 of which are reciprocated. So we are working with 83 - 13, which is 70
#MemberTypeChargeType links the MemberTypeId with the ChargeTypeId with
67 Members are of type MemberId = 1 - The Charge Types applicable are 1 & 3
3 Members are of Type MemberId = 2 - The Charge Type applicable is 2
Charge type 1 has two levels. For the first 10, the charge is 6 per person(60), but for the next 57, it is 3 per person(171)
Charge Type 2 only has one level, and there are 3 people at 8 per person(24)
In table #ChargeTypes, there is a column called AGroupRate. When this is false, the charges are per person.
The tricky one is Charge type 3, as it is based on a group rate based on the total number of MemberTypeId = 1 or any other MemberTypeId linked to it.
In our case, we have a total of 67, and this fits into the slot where FromCount to ToCount is 51 to 9999, so the charge is 0
Is this possible in SQL - I am trying to avoid hard-coded values, but it is okay if there is more than one SQL query.
I need it in a stored procedure where I supply the order @ID, @EarlyBird and @Reciprocate as parameters.
My current method works, but I have hard-coded some of the values in the SQL, and I am trying to move away from that - I think it is possible, but my less elegant solution is working, so I am finding it hard to see the workaround.
I have full control of the tables #MemberTypes, #ChargeTypes, #MemberTypeChargeType, #ChargeRates So, if you think I should reconstruct them in a way that would make achieving my objective better, that would be an option. The result should remain the same - and the ID values should also stay the same.
below is what I am doing - as you can see, it requires me to know what types there are, and I am using the tables just to store the values currently. So the prices and ranges can change but not the types themselves
declare
@orderId int = 129
,@orderYear int = null
,@orderTotal decimal(18,2) = null
,@debug bit = 1
,@earlyBird bit = 1
,@reciprocate bit = 1
declare @regCount int
,@docCount int
,@totCount int
,@regCost decimal(18,2)
,@docCost decimal(18,2)
,@recipCost decimal(18,2)
,@totCost decimal(18,2)
select @regCount = (select count(*) from #OrderItems o2 where o2.OrderId = @orderId and o2.MemberTypeId = 1 and o2.Reciprocated = 0)
,@docCount = (select count(*) from #OrderItems o2 where o2.OrderId = @orderId and o2.MemberTypeId = 2)
,@totCount = (select count(*) from #OrderItems o2 where o2.OrderId = @orderId and o2.Reciprocated = 0)
if @debug = 1
begin
select
@orderId [@orderId]
,@regCount [@regCount]
,@docCount [@docCount]
,@totCount [@totCount]
,@earlyBird [@earlyBird]
,@reciprocate [@reciprocate]
end
select
@regCost = sum(case when @regCount >= cr.FromCount and cr.ChargeTypeId = 1 then (case when cr.ToCount < @regCount then cr.ToCount else @regCount - (cr.FromCount -1) end) * cr.RatePrice else 0 end)
,@docCost = sum(case when @docCount >= cr.FromCount and cr.ChargeTypeId = 2 then (case when cr.ToCount < @docCount then cr.ToCount else @docCount - (cr.FromCount -1) end) * cr.RatePrice else 0 end)
,@recipCost = sum(case when @totCount > 0 and cr.ChargeTypeId = 3 and @reciprocate = 1 then case when @totCount between cr.FromCount and cr.ToCount then cr.RatePrice else 0 end else 0 end)
from #ChargeRates cr
where cr.EarlyBird = @earlyBird
if @debug = 1
select @regCost
,@docCost
,@recipCost
set @totCost = @regCost + @docCost + @recipCost
if @debug = 1
begin
select
@regCost [@regCost]
,@docCost [@docCost]
,@recipCost [@recipCost]
,@totCost [@totCost]
end
select @totCost;
For the 2 different logic for ChargeTypeId 1,2 and 3, I have split into 2 separate cte (cte1 and cte2) and union all for the final result.
The different in the logic is at the ON condition when joining to ChargeRates
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