Calculating percentages based on multiple rows.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating percentages based on multiple rows.

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-02 : 11:53:39

I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.

Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?

Category Example

ID	AmazonCategoryID	AmazonCategoryName	FeePercentage	StartPrice	EndPrice
1	aps	AllDepartments	0.15	0.00	0.00
2	instant-video	AmazonInstantVideo	0.00	0.00	0.00
3	appliances	Appliances	0.15	0.00	0.00

Product Example

1	Product1	Electronics	9.99
2	Product3	Electronics	99.99
3	Product2	Electronics	599.99

Raw SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AmazonCategoryID] [nvarchar](50) NULL,
	[AmazonCategoryName] [nvarchar](50) NULL,
	[FeePercentage] [decimal](18, 2) NULL,
	[StartPrice] [decimal](18, 2) NULL,
	[EndPrice] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT #Amzn_Category_FeeStructure ON
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (1, N'aps', N'AllDepartments', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (2, N'instant-video', N'AmazonInstantVideo', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (3, N'appliances', N'Appliances', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (4, N'mobile-apps', N'Apps&Games', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (5, N'arts-crafts', N'Arts,Crafts&Sewing', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (6, N'automotive', N'Automotive', CAST(0.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (7, N'baby-products', N'Baby', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (8, N'beauty', N'Beauty', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (9, N'stripbooks', N'Books', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (10, N'popular', N'CDs&Vinyl', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (11, N'mobile', N'CellPhones&Accessories', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (12, N'fashion', N'Clothing,Shoes&Jewelry', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (13, N'fashion-womens', N'Women', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (14, N'fashion-mens', N'Men', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (15, N'fashion-girls', N'Girls', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (16, N'fashion-boys', N'Boys', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (17, N'fashion-baby', N'Baby', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (18, N'collectibles', N'Collectibles&FineArt', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (19, N'computers', N'Computers', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (20, N'digital-music', N'DigitalMusic', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (21, N'electronics', N'Electronics', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(100.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (22, N'gift-cards', N'GiftCardsStore', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (23, N'grocery', N'Grocery&GourmetFood', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (24, N'hpc', N'Health&PersonalCare', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (25, N'garden', N'Home&Kitchen', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (26, N'industrial', N'Industrial&Scientific', CAST(0.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (27, N'digital-text', N'KindleStore', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (28, N'fashion-luggage', N'Luggage&TravelGear', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (29, N'magazines', N'MagazineSubscriptions', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (30, N'movies-tv', N'Movies&TV', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (31, N'mi', N'MusicalInstruments', CAST(0.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (32, N'office-products', N'OfficeProducts', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (33, N'lawngarden', N'Patio,Lawn&Garden', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (34, N'pets', N'PetSupplies', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (35, N'pantry', N'PrimePantry', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (36, N'software', N'Software', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (37, N'sporting', N'Sports&Outdoors', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (38, N'tools', N'Tools&HomeImprovement', CAST(0.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (39, N'toys-and-games', N'Toys&Games', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (40, N'videogames', N'VideoGames', CAST(0.15 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (41, N'wine', N'Wine', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT #Amzn_Category_FeeStructure ([ID], [AmazonCategoryID], [AmazonCategoryName], [FeePercentage], [StartPrice], [EndPrice]) VALUES (42, N'electronics', N'Electronics', CAST(8.00 AS Decimal(18, 2)), CAST(100.00 AS Decimal(18, 2)), CAST(999999.00 AS Decimal(18, 2)))
SET IDENTITY_INSERT #Amzn_Category_FeeStructure OFF



CREATE TABLE #Amzn_Product(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [nvarchar](50) NULL,
	[Category] [nvarchar](50) NULL,
	[ProductPrice] [decimal](18, 2) NULL,
) ON [PRIMARY]
GO

INSERT #Amzn_Product ([ProductID], [Category], [ProductPrice]) VALUES ('Product1','Electronics','9.99')
INSERT #Amzn_Product ([ProductID], [Category], [ProductPrice]) VALUES ('Product3','Electronics','99.99')
INSERT #Amzn_Product ([ProductID], [Category], [ProductPrice]) VALUES ('Product2','Electronics','599.99')

Select * from #Amzn_Category_FeeStructure
Select * from #Amzn_Product

-Sergio
I use Microsoft SQL 2008

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-02 : 16:10:58

1) Your data is messed up. In Category.ID = 21 you use 0.15 for 15% but in Category.ID = 41 you use 8.00 for 8%
2) Your endpoints overlap. If something costs exactly $100.00 it would get two fees applied

;with PartialFees
as (
select p.*,
    (
    case when p.ProductPrice > c.EndPrice 
         then c.EndPrice
         else p.ProductPrice
         end - c.StartPrice
    ) * c.FeePercentage Fee
from #Amzn_Product p
inner join
    #Amzn_Category_FeeStructure c
        on  p.Category = c.AmazonCategoryName
        and (
            p.ProductPrice > c.EndPrice
            or
            p.ProductPrice between c.StartPrice and c.EndPrice
            )
)            
select pf.ProductID, pf.Category, pf.ProductPrice, sum(pf.Fee) Fees
from PartialFees pf
group by
    pf.ProductID, pf.Category, pf.ProductPrice

No amount of belief makes something a fact. -James Randi

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-04 : 12:24:08

Thanks on several counts! I'll make the corrections & the query does exactly what I need. I really appreciate the help!

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources