May 15, 2011 at 12:29 pm
We have a NET SALES table (customer, calendar_month, net_sales),
We classify customers based on the net_sales accumulated over a 12 month period, eg. customer with net_sales over $3,000 is gold customers;
I need to create a table that will reflect the count of customers in a specified classification in a selected period (eg. we had 6,700 gold customers in May-2011)
Any suggestions or solutions will be appreciated,
May 15, 2011 at 12:45 pm
thysvdw (5/15/2011)
We have a NET SALES table (customer, calendar_month, net_sales),We classify customers based on the net_sales accumulated over a 12 month period, eg. customer with net_sales over $3,000 is gold customers;
I need to create a table that will reflect the count of customers in a specified classification in a selected period (eg. we had 6,700 gold customers in May-2011)
If you only store the calender month you will not be able to differentiate between years.
Don't you need a classification type table that is a parent to the table that you are referring to?
How do you define Period, by calendar quarter, fiscal quarter, etc?
Do you have additional sample data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 2:18 pm
The classification table is existing as a dictionary table, and we are able to determine all relevant date periods from the financial_period (calendar_month) column
Sample data attached
May 15, 2011 at 2:24 pm
thysvdw (5/15/2011)
The classification table is existing as a dictionary table, and we are able to determine all relevant date periods from the financial_period (calendar_month) columnSample data attached
Could you please provide the table definitions for each table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 2:35 pm
NET_SALES and DIC_CUSTOMER_CLASSIFICATION table definitions attached!
May 15, 2011 at 2:39 pm
thysvdw (5/15/2011)
NET_SALES and DIC_CUSTOMER_CLASSIFICATION table definitions attached!
That is an ugly definition..:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 2:43 pm
I'm open to suggestions!
May 15, 2011 at 2:50 pm
I would not provide the table definitions without all the extras, collation, etc.
Submit a scipt the way that you create a table(s) in T-SQL.
Regards.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 3:46 pm
You might want to have a look at the first article referenced in my signature for a guide on how to post ready to use sample data.
May 16, 2011 at 8:02 am
Here's your sample data in a readily consumable format: -
--==Sample Data==--
-- check if table exists
IF OBJECT_ID('tempdb..#net_sales') IS NOT NULL
DROP TABLE #net_sales
-- create temporary table
CREATE TABLE #net_sales(
[dic_company_key] [tinyint] NULL,
[dim_customer_key] [int] NULL,
[financial_period] [varchar](7) NULL,
[net_sales] [money] NULL)
insert into #net_sales
select '1','2','F200811','602.00' UNION ALL
select '1','2','F200901','472.00' UNION ALL
select '1','2','F200903','589.00' UNION ALL
select '1','2','F200904','719.00' UNION ALL
select '1','2','F200905','481.00' UNION ALL
select '1','2','F200906','198.00' UNION ALL
select '1','2','F200907','189.00' UNION ALL
select '1','2','F200908','316.00' UNION ALL
select '1','2','F200909','0.00' UNION ALL
select '1','2','F200911','311.00' UNION ALL
select '1','2','F200912','234.00' UNION ALL
select '1','2','F201003','329.00' UNION ALL
select '1','2','F201004','251.00' UNION ALL
select '1','2','F201010','225.00' UNION ALL
select '1','2','F201101','333.00' UNION ALL
select '1','2','F201102','355.00' UNION ALL
select '1','2','F201110','0.00' UNION ALL
select '1','4','F200908','398.00' UNION ALL
select '1','12','F200807','302.00' UNION ALL
select '1','16','F200810','173.00' UNION ALL
select '1','24','F200808','172.00' UNION ALL
select '1','24','F200904','43.00' UNION ALL
select '1','25','F201001','86.00' UNION ALL
select '1','38','F200901','152.00' UNION ALL
select '1','38','F200902','320.00' UNION ALL
select '1','43','F201005','113.00' UNION ALL
select '1','75','F200910','60.00' UNION ALL
select '1','93','F200903','1065.00' UNION ALL
select '1','93','F201009','86.00' UNION ALL
select '1','116','F201004','26.00' UNION ALL
select '1','119','F200904','56.00' UNION ALL
select '1','119','F200905','129.00' UNION ALL
select '1','119','F201002','26.00' UNION ALL
select '1','119','F201004','112.00' UNION ALL
select '1','119','F201007','42.00' UNION ALL
select '1','119','F201011','0.00' UNION ALL
select '1','119','F201101','0.00' UNION ALL
select '1','119','F201103','0.00' UNION ALL
select '1','122','F200905','95.00' UNION ALL
select '1','122','F201105','0.00' UNION ALL
select '1','130','F201008','86.00' UNION ALL
select '1','131','F200807','424.00' UNION ALL
select '1','131','F200812','276.00' UNION ALL
select '1','131','F200903','450.00' UNION ALL
select '1','131','F200905','152.00' UNION ALL
select '1','131','F200908','164.00' UNION ALL
select '1','131','F200910','346.00' UNION ALL
select '1','150','F200808','164.00' UNION ALL
select '1','150','F200809','346.00' UNION ALL
select '1','150','F200810','433.00' UNION ALL
select '1','150','F200811','286.00' UNION ALL
select '1','150','F200812','424.00' UNION ALL
select '1','150','F200901','294.00' UNION ALL
select '1','150','F200904','303.00' UNION ALL
select '1','150','F200908','199.00' UNION ALL
select '1','150','F200909','15.00' UNION ALL
select '1','150','F201009','303.00' UNION ALL
select '1','150','F201103','364.00' UNION ALL
select '1','150','F201104','0.00' UNION ALL
select '1','152','F200906','86.00' UNION ALL
select '1','162','F200812','276.00' UNION ALL
select '1','180','F201001','86.00' UNION ALL
select '1','180','F201004','130.00' UNION ALL
select '1','180','F201005','-132.00' UNION ALL
select '1','184','F200809','753.00' UNION ALL
select '1','184','F200810','649.00' UNION ALL
select '1','184','F200903','329.00' UNION ALL
select '1','184','F200907','129.00' UNION ALL
select '1','184','F200908','768.00' UNION ALL
select '1','184','F200909','423.00' UNION ALL
select '1','184','F200910','0.00' UNION ALL
select '1','190','F200905','43.00' UNION ALL
select '1','228','F200805','17.00' UNION ALL
select '1','228','F200807','727.00' UNION ALL
select '1','239','F200809','0.00' UNION ALL
select '1','239','F200810','130.00' UNION ALL
select '1','239','F201007','319.00' UNION ALL
select '1','255','F200807','718.00' UNION ALL
select '1','255','F200808','907.00' UNION ALL
select '1','255','F200810','1533.00' UNION ALL
select '1','255','F200811','641.00' UNION ALL
select '1','255','F200812','1013.00' UNION ALL
select '1','255','F200901','173.00' UNION ALL
select '1','255','F200902','611.00' UNION ALL
select '1','255','F200904','233.00' UNION ALL
select '1','255','F200906','743.00' UNION ALL
select '1','255','F200907','879.00' UNION ALL
select '1','255','F200908','868.00' UNION ALL
select '1','255','F200909','665.00' UNION ALL
select '1','255','F200911','285.00' UNION ALL
select '1','255','F201001','1141.00' UNION ALL
select '1','255','F201005','87.00' UNION ALL
select '1','255','F201008','268.00' UNION ALL
select '1','298','F200808','172.00' UNION ALL
select '1','298','F200810','169.00' UNION ALL
select '1','298','F200812','87.00' UNION ALL
select '1','298','F200901','225.00' UNION ALL
select '1','298','F200905','381.00' UNION ALL
select '1','298','F201004','112.00' UNION ALL
select '1','298','F201005','303.00'
-- check if table exists
IF OBJECT_ID('tempdb..#classification') IS NOT NULL
DROP TABLE #classification
-- create table for customer classification
CREATE TABLE #classification (
[dic_customer_classification_key] [tinyint] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[dic_company_key] [tinyint] NOT NULL,
[customer_classification_code] [char](3) NOT NULL,
[customer_classification_description] [char](15) NOT NULL,
[customer_classification_value] [smallmoney] NOT NULL,
[customer_classification_quantity] [smallint] NOT NULL,
[customer_classification_period] [tinyint] NOT NULL,
[customer_classification_colour_code] [int] NOT NULL)
-- all inserts into IDENTITY column
set identity_insert #classification ON
-- create data for classification table
insert into #classification (
[dic_customer_classification_key],[dic_company_key],[customer_classification_code],[customer_classification_description],[customer_classification_value]
,[customer_classification_quantity],[customer_classification_period],[customer_classification_colour_code])
select'1','1','001','PLATINUM','3000.00','6449','12','16711935' UNION ALL
select'2','1','002','GOLD','2000.00','3322','12','33023' UNION ALL
select'3','1','003','SILVER','1500.00','2807','12','12632256' UNION ALL
select'4','1','004','BRONZE','800.00','7572','12','16512' UNION ALL
select'5','2','001','PLATINUM','3000.00','0','12','16711935' UNION ALL
select'6','2','002','GOLD','2000.00','0','12','33023' UNION ALL
select'7','2','003','SILVER','1500.00','0','12','12632256' UNION ALL
select'8','2','004','BRONZE','800.00','0','12','16512'
-- set identity insert back to normal
set identity_insert #classification OFF
I'm sure there's a better way to do this, but here's my attempt with your sample data: -
SELECT a.dic_company_key, a.dim_customer_key, a.date_financial_period,
a.net_sales, b.customer_classification_description
FROM (SELECT a.dic_company_key, a.dim_customer_key, a.date_financial_period,
a.net_sales, MIN(CONVERT(INT,b.customer_classification_code)) AS code
FROM (SELECT
CONVERT(DATETIME,REPLACE(financial_period,'F','')+'01') AS date_financial_period,
SUM(net_sales) AS net_sales, dim_customer_key, dic_company_key
FROM #net_sales
GROUP BY CONVERT(DATETIME,REPLACE(financial_period,'F','')+'01'), dim_customer_key,
dic_company_key) a
LEFT OUTER JOIN #classification b ON a.dic_company_key = b.dic_company_key AND a.net_sales >= b.customer_classification_value
WHERE b.customer_classification_description IS NOT NULL
GROUP BY a.dic_company_key, a.dim_customer_key, a.date_financial_period,
a.net_sales) a
LEFT OUTER JOIN #classification b ON a.dic_company_key = b.dic_company_key AND a.code = CONVERT(INT,b.customer_classification_code)
ORDER BY a.date_financial_period
/*
--==Results==-
dic_company_key dim_customer_key date_financial_period net_sales customer_classification_description
--------------- ---------------- ----------------------- --------------------- -----------------------------------
1 255 2008-08-01 00:00:00.000 907.00 BRONZE
1 255 2008-10-01 00:00:00.000 1533.00 SILVER
1 255 2008-12-01 00:00:00.000 1013.00 BRONZE
1 93 2009-03-01 00:00:00.000 1065.00 BRONZE
1 255 2009-07-01 00:00:00.000 879.00 BRONZE
1 255 2009-08-01 00:00:00.000 868.00 BRONZE
1 255 2010-01-01 00:00:00.000 1141.00 BRONZE
*/
I converted your financial period into a DATETIME, because it's easier later on to do date based reports. You can change it back if you wish.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply