May 24, 2013 at 5:20 pm
I need to GROUP products by category based on a product rate, but I'm having a hard time joining on multiple tables.
Here's the layout...
A customer has N number of products that are billed at N number of rates. Each product rate for a customer has a record in the [custrate] table. There are also two other tables that store the rate data...[rateheader] and [rate]. The [rateheader] table is the parent...[rate] and [custrate] are child tables.
Now here's where it get's fuzzy... The only table of these three that has the [category_id] is the [rate] table. This table holds the base product rate record and N number of product override rate records. These records share the same [rg_id] but have different [idnum]'s. The base rate record always has an [idnum] = 0 and he product override rate records have [idnum] = [crid]. This [crid] is the primary key in the [custrate] table. So...I can almost join on [idnum] since it has unique [crid] values for the override records...but with the base rate records [idnum] = 0...I can't. Well...I don't know if I can or not...
Here are all tables involved...some sample data and my failed query.
USE Test
GO
CREATE TABLE [dbo].[customer](
[cust_id] [int] NOT NULL,
[active] [char](1) NULL,
[storeid] [int] NULL,
CONSTRAINT [pk_customer] PRIMARY KEY CLUSTERED
(
[cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[customer]([cust_id], [active], [storeid])
SELECT 1, 'Y', 1 UNION ALL
SELECT 2, 'Y', 1 UNION ALL
SELECT 3, 'Y', 1 UNION ALL
SELECT 4, 'Y', 1 UNION ALL
SELECT 5, 'Y', 2 UNION ALL
SELECT 6, 'Y', 2 UNION ALL
SELECT 7, 'Y', 2 UNION ALL
SELECT 8, 'Y', 2
GO
CREATE TABLE [dbo].[custrate](
[crid] [int] NOT NULL,
[cust_id] [int] NOT NULL,
[rg_id] [smallint] NOT NULL,
[lastbilled] [datetime] NULL,
[nextbill] [datetime] NULL,
CONSTRAINT [pk_custrate] PRIMARY KEY CLUSTERED
(
[crid] 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
INSERT INTO [dbo].[custrate]([crid], [cust_id], [rg_id], [lastbilled], [nextbill])
SELECT 111, 1, 2001, '20130504 00:00:00.000', '20130604 00:00:00.000' UNION ALL
SELECT 112, 2, 2001, '20130519 00:00:00.000', '20130619 00:00:00.000' UNION ALL
SELECT 113, 2, 2002, '20120522 10:13:39.000', '20130522 00:00:00.000' UNION ALL
SELECT 114, 2, 2003, '20120211 00:00:00.000', '20120511 00:00:00.000' UNION ALL
SELECT 115, 3, 2003, '20130516 00:00:00.000', '20130616 00:00:00.000' UNION ALL
SELECT 116, 3, 2004, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALL
SELECT 117, 4, 2002, '20120821 00:00:00.000', '20120921 00:00:00.000' UNION ALL
SELECT 118, 5, 2001, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALL
SELECT 119, 5, 2001, '20120701 00:00:00.000', '20120801 00:00:00.000' UNION ALL
SELECT 120, 6, 2001, '20130521 00:00:00.000', '20130621 00:00:00.000' UNION ALL
SELECT 121, 6, 2004, '20121012 00:00:00.000', '20121112 00:00:00.000' UNION ALL
SELECT 122, 7, 2001, '20130520 00:00:00.000', '20140520 00:00:00.000' UNION ALL
SELECT 123, 8, 2002, '20120704 00:00:00.000', '20120804 00:00:00.000' UNION ALL
SELECT 124, 8, 2003, '20120810 00:00:00.000', '20120910 00:00:00.000' UNION ALL
SELECT 125, 8, 2004, '20120622 00:00:00.000', '20120722 00:00:00.000'
GO
CREATE TABLE [dbo].[rateheader](
[rg_id] [int] NOT NULL,
[rg_name] [varchar](100) NOT NULL,
CONSTRAINT [pk_rateheader] PRIMARY KEY CLUSTERED
(
[rg_id] 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
INSERT INTO [dbo].[rateheader]([rg_id], [rg_name])
SELECT 2001, N'Product1 Rate' UNION ALL
SELECT 2002, N'Product2 Rate' UNION ALL
SELECT 2003, N'Product3 Rate' UNION ALL
SELECT 2004, N'Product4 Rate'
GO
CREATE TABLE [dbo].[rate](
[id] [int] NOT NULL,
[rg_id] [int] NOT NULL,
[idnum] [int] NULL,
[name] [varchar](80) NOT NULL,
[price] [money] NOT NULL,
[category_id] [int] NULL,
CONSTRAINT [pk_rate] PRIMARY KEY CLUSTERED
(
[id] 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
INSERT INTO [dbo].[rate]([id], [rg_id], [idnum], [name], [price], [category_id])
SELECT 1, 2001, 0, 'Product1 Base Rate', 100.99, 1001 UNION ALL
SELECT 2, 2001, 111, 'Product1 Override Rate', 200.99, 1001 UNION ALL
SELECT 3, 2001, 112, 'Product1 Override Rate', 700.99, 1001 UNION ALL
SELECT 4, 2001, 118, 'Product1 Override Rate', 20.99, 1001 UNION ALL
SELECT 5, 2001, 119, 'Product1 Override Rate', 800.99, 1001 UNION ALL
SELECT 6, 2002, 0, 'Product2 Base Rate', 99.99, 1002 UNION ALL
SELECT 7, 2002, 123, 'Product2 Override Rate', 100.00, 1002 UNION ALL
SELECT 8, 2003, 0, 'Product3 Base Rate', 1.99, 1003 UNION ALL
SELECT 9, 2003, 115, 'Product3 Override Rate', 5.99, 1003 UNION ALL
SELECT 10, 2003, 124, 'Product3 Override Rate', 6.99, 1003 UNION ALL
SELECT 11, 2004, 0, 'Product4 Base Rate', 999.00, 1004 UNION ALL
SELECT 12, 2004, 125, 'Product4 Override Rate', 1200.99, 1004
GO
CREATE TABLE [dbo].[product_category](
[category_id] [int] NOT NULL,
[category_name] [varchar](250) NOT NULL,
CONSTRAINT [pk_item_category] PRIMARY KEY CLUSTERED
(
[category_id] 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
INSERT INTO [dbo].[product_category]([category_id], [category_name])
SELECT 1001, N'Product1' UNION ALL
SELECT 1002, N'Product2' UNION ALL
SELECT 1003, N'Product3' UNION ALL
SELECT 1004, N'Product4'
-- Group all product rates by category
SELECT pc.category_name, count(*) as Total
-- c.storeid, c.cust_id, rh.rg_name, r.idnum, r.category_id, cr.*
FROM customer c WITH(NOLOCK)
JOIN custrate cr WITH(NOLOCK) ON cr.cust_id = c.cust_id AND c.active = 'Y' AND c.storeid IN (1,2)
JOIN rateheader rh WITH(NOLOCK) ON cr.rg_id = rh.rg_id
LEFT JOIN rate r WITH(NOLOCK) ON r.idnum = cr.crid
LEFT JOIN product_category pc WITH(NOLOCK) ON r.category_id = pc.category_id
GROUP BY pc.category_name
May 24, 2013 at 5:31 pm
*scratches head*
Okay, you've officially confused me... and I thought I understood it, so bear with me. Is my assumption correct that the rate table has a 'minimum bought' column of some kind that tells it the thresholds that need to be applied based on the amount the customer is getting, and thus the link via rg_id is then dependant on order amounts? Because otherwise I'm having a real hard time figuring out how a customer is assigned a rate when they're getting four different ones at once.
I think that piece is why nothing's falling together for you, either way.
Now, my other question is what are you trying to get out of this? From this sample data, what would you expect (or hope to extract)? I'm not clear on what you want to do once you group by the categories in rate. You've got a count going, but obviously that's not what you're hoping to get.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2013 at 5:51 pm
OK... Here goes...
The rateheader table has all of the unique rates for all products. The rate table has all of those same base rate records with idnum = 0. When a customer orders a product that has different rate than the base product rate record...it adds the new price override records to the custrate table first and then to the rate table. The idnum of the new records in the rate table are set to the crid from custrate. Why it does this...I have no idea...
For now...my end goal is to simply report on the number of products in each category.
Sorry if my explanations are confusing...it's because this issue has fully confused me.. 😀
May 24, 2013 at 6:04 pm
Well, I think this gets you where you need to be:
SELECT
c.Category_Name,
COUNT(*) AS Total
FROM
(SELECT DISTINCT
category_id,
rg_id
FROM
#rate
) AS drv
JOIN
#product_category AS c
ONdrv.Category_id = c.Category_id
GROUP BY
c.Category_Name
I converted things to #tables so if you need those:
CREATE TABLE [dbo].[#customer](
[cust_id] [int] NOT NULL,
[active] [char](1) NULL,
[storeid] [int] NULL,
CONSTRAINT [pk_customer] PRIMARY KEY CLUSTERED
(
[cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[#customer]([cust_id], [active], [storeid])
SELECT 1, 'Y', 1 UNION ALL
SELECT 2, 'Y', 1 UNION ALL
SELECT 3, 'Y', 1 UNION ALL
SELECT 4, 'Y', 1 UNION ALL
SELECT 5, 'Y', 2 UNION ALL
SELECT 6, 'Y', 2 UNION ALL
SELECT 7, 'Y', 2 UNION ALL
SELECT 8, 'Y', 2
GO
CREATE TABLE [dbo].[#custrate](
[crid] [int] NOT NULL,
[cust_id] [int] NOT NULL,
[rg_id] [smallint] NOT NULL,
[lastbilled] [datetime] NULL,
[nextbill] [datetime] NULL,
CONSTRAINT [pk_custrate] PRIMARY KEY CLUSTERED
(
[crid] 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
INSERT INTO [dbo].[#custrate]([crid], [cust_id], [rg_id], [lastbilled], [nextbill])
SELECT 111, 1, 2001, '20130504 00:00:00.000', '20130604 00:00:00.000' UNION ALL
SELECT 112, 2, 2001, '20130519 00:00:00.000', '20130619 00:00:00.000' UNION ALL
SELECT 113, 2, 2002, '20120522 10:13:39.000', '20130522 00:00:00.000' UNION ALL
SELECT 114, 2, 2003, '20120211 00:00:00.000', '20120511 00:00:00.000' UNION ALL
SELECT 115, 3, 2003, '20130516 00:00:00.000', '20130616 00:00:00.000' UNION ALL
SELECT 116, 3, 2004, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALL
SELECT 117, 4, 2002, '20120821 00:00:00.000', '20120921 00:00:00.000' UNION ALL
SELECT 118, 5, 2001, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALL
SELECT 119, 5, 2001, '20120701 00:00:00.000', '20120801 00:00:00.000' UNION ALL
SELECT 120, 6, 2001, '20130521 00:00:00.000', '20130621 00:00:00.000' UNION ALL
SELECT 121, 6, 2004, '20121012 00:00:00.000', '20121112 00:00:00.000' UNION ALL
SELECT 122, 7, 2001, '20130520 00:00:00.000', '20140520 00:00:00.000' UNION ALL
SELECT 123, 8, 2002, '20120704 00:00:00.000', '20120804 00:00:00.000' UNION ALL
SELECT 124, 8, 2003, '20120810 00:00:00.000', '20120910 00:00:00.000' UNION ALL
SELECT 125, 8, 2004, '20120622 00:00:00.000', '20120722 00:00:00.000'
GO
CREATE TABLE [dbo].[#rateheader](
[rg_id] [int] NOT NULL,
[rg_name] [varchar](100) NOT NULL,
CONSTRAINT [pk_rateheader] PRIMARY KEY CLUSTERED
(
[rg_id] 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
INSERT INTO [dbo].[#rateheader]([rg_id], [rg_name])
SELECT 2001, N'Product1 Rate' UNION ALL
SELECT 2002, N'Product2 Rate' UNION ALL
SELECT 2003, N'Product3 Rate' UNION ALL
SELECT 2004, N'Product4 Rate'
GO
CREATE TABLE [dbo].[#rate](
[id] [int] NOT NULL,
[rg_id] [int] NOT NULL,
[idnum] [int] NULL,
[name] [varchar](80) NOT NULL,
[price] [money] NOT NULL,
[category_id] [int] NULL,
CONSTRAINT [pk_rate] PRIMARY KEY CLUSTERED
(
[id] 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
INSERT INTO [dbo].[#rate]([id], [rg_id], [idnum], [name], [price], [category_id])
SELECT 1, 2001, 0, 'Product1 Base Rate', 100.99, 1001 UNION ALL
SELECT 2, 2001, 111, 'Product1 Override Rate', 200.99, 1001 UNION ALL
SELECT 3, 2001, 112, 'Product1 Override Rate', 700.99, 1001 UNION ALL
SELECT 4, 2001, 118, 'Product1 Override Rate', 20.99, 1001 UNION ALL
SELECT 5, 2001, 119, 'Product1 Override Rate', 800.99, 1001 UNION ALL
SELECT 6, 2002, 0, 'Product2 Base Rate', 99.99, 1002 UNION ALL
SELECT 7, 2002, 123, 'Product2 Override Rate', 100.00, 1002 UNION ALL
SELECT 8, 2003, 0, 'Product3 Base Rate', 1.99, 1003 UNION ALL
SELECT 9, 2003, 115, 'Product3 Override Rate', 5.99, 1003 UNION ALL
SELECT 10, 2003, 124, 'Product3 Override Rate', 6.99, 1003 UNION ALL
SELECT 11, 2004, 0, 'Product4 Base Rate', 999.00, 1004 UNION ALL
SELECT 12, 2004, 125, 'Product4 Override Rate', 1200.99, 1004
GO
CREATE TABLE [dbo].[#product_category](
[category_id] [int] NOT NULL,
[category_name] [varchar](250) NOT NULL,
CONSTRAINT [pk_item_category] PRIMARY KEY CLUSTERED
(
[category_id] 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
INSERT INTO [dbo].[#product_category]([category_id], [category_name])
SELECT 1001, N'Product1' UNION ALL
SELECT 1002, N'Product2' UNION ALL
SELECT 1003, N'Product3' UNION ALL
SELECT 1004, N'Product4'
I think I'd need to actually see the database to understand exactly what you're doing with the rates.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2013 at 6:22 pm
Let me rephrase that...I'm actually looking for the total number of product *rates* for each product category. That's why I've included the custrate table in the example. A count of records from that table is what I need...but what I can't figure out is how to join through to the product_category table.
I hope that makes more sense...
May 24, 2013 at 6:28 pm
brickpack (5/24/2013)
Let me rephrase that...I'm actually looking for the total number of product *rates* for each product category. That's why I've included the custrate table in the example. A count of records from that table is what I need...but what I can't figure out is how to join through to the product_category table.I hope that makes more sense...
I think so, actually.
So, it'd look like this?
SELECT
c.Category_Name,
COUNT(*) AS Total
FROM
(SELECT DISTINCT
category_id,
rg_id
FROM
#rate
) AS drv
JOIN
#product_category AS c
ONdrv.Category_id = c.Category_id
JOIN
#CustRate AS cr
ONcr.rg_id = drv.rg_id
GROUP BY
c.Category_Name
Ignore that, the counts are off, I'm getting 15 results when there's only 12 rates in CustRate. Nope, that was right. I need to learn to count. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2013 at 6:32 pm
Out of curiosity, can you move category_id up to #rateheader? That seems like the most logical place for it long-term, and means you won't end up with one of these in multiple categories.
EDIT: I'm also leaving for the long weekend shortly. Good luck! Hopefully someone else can pickup where I left off to help you find your answer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply