May 8, 2012 at 5:57 pm
I have the following tables:
Products
(Product_id,Product_Name,Product_Type,Cost_Price)
Sales
(SalesOrder_id, Product_id,Quantity, Sell_Price, Sale_Date)
I want to produce a query that provides most profitable products, per year,week,month,day and overall.
I can do these queries individually for example the top 5 profitable products per month
SELECT TOP 5 P.Product_Name,DATEPART(MM,S.SaleDate) AS MoNTH(S.Sell_Price P.Cost_Price) * S.Quantity AS Profit
FROM Products P INNER JOIN Sales S
ON S.Product_id = P.Product_id
GROUP BY P.Product_Name,DATEPART(MM,S.SaleDate)
Is there a way to produce an output with profitable products per week, year etc in one query?
May 8, 2012 at 6:24 pm
If there is sample data, Create table structure attached with your post - Then YES Of course!
May 8, 2012 at 7:19 pm
CREATE TABLE PRODUCTS(
Product_id int CONSTRAINT pk_products_pid PRIMARY KEY
,ProductName varchar(10) null
,ProductType varchar(10) null
,cost_price float null
)
CREATE TABLE SALES(
SalesOrder_id int CONSTRAINT pk_Orders_sid PRIMARY KEY,
Product_id INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(Product_id),
,quantity int null
,sales_price float null
,saledate datetime null
)
insert into Products (product_id, productname,producttype,cost_price)
select 1, tomato, veg, 55
union all
select 2, apple, fruit, 40
union all
select 3, peas, veg, 25
union all
select 4, orange, fruit, 30
union all
select 5, peach, fruit, 35
insert into Sales (SalesOrder_id,product_id, quantity,sales_price,saledate)
select 1,4, 10, 45, 03052012
union all
select 2,4, 5, 45, 08042012
union all
select 3,2, 6, 50, 15032012
union all
select 4,2, 3, 50, 08022012
union all
select 5,1, 10, 58, 29012012
union all
select 6,1, 10, 58, 24042012
union all
May 8, 2012 at 9:28 pm
And expected resutls?
May 8, 2012 at 10:57 pm
Use this query
-- Month wise
Select P.ProductName , P.ProductType ,
DATENAME(month, S.saledate ) +' '+CONVERT (VARCHAR,Year(s.saledate )) as 'Month Name' ,
SUM((S.sales_price -cost_price)*S.quantity ) As Profit
FROM PRODUCTS P
INNER JOIN SALES S ON P.Product_id =S.Product_id
GROUP BY P.ProductName , P.ProductType , DATENAME(month, S.saledate ) +' '+CONVERT (VARCHAR,Year(s.saledate ))
order by SUM((S.sales_price -cost_price)*S.quantity ) DESC
Year and week wise can be get by group on the basis of Year or week
May 9, 2012 at 7:56 am
I think that T-SQL is simply the wrong tool for this particular job. T-SQL is designed to optimize transactional processing. While you can do basic analytical processing in T-SQL, complex OLAP is better done in a tool specifically designed to do OLAP such as SSAS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 9, 2012 at 8:19 am
Is there a way to produce an output with profitable products per week, year etc in one query?
There is a way!
But, how exactly do you want results to look like?
May 9, 2012 at 8:44 am
eseosaoregie (5/8/2012)
CREATE TABLE PRODUCTS(
Product_id int CONSTRAINT pk_products_pid PRIMARY KEY
,ProductName varchar(10) null
,ProductType varchar(10) null
,cost_price float null
)
CREATE TABLE SALES(
SalesOrder_id int CONSTRAINT pk_Orders_sid PRIMARY KEY,
Product_id INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(Product_id),
,quantity int null
,sales_price float null
,saledate datetime null
)
insert into Products (product_id, productname,producttype,cost_price)
select 1, tomato, veg, 55
union all
select 2, apple, fruit, 40
union all
select 3, peas, veg, 25
union all
select 4, orange, fruit, 30
union all
select 5, peach, fruit, 35
insert into Sales (SalesOrder_id,product_id, quantity,sales_price,saledate)
select 1,4, 10, 45, 03052012
union all
select 2,4, 5, 45, 08042012
union all
select 3,2, 6, 50, 15032012
union all
select 4,2, 3, 50, 08022012
union all
select 5,1, 10, 58, 29012012
union all
select 6,1, 10, 58, 24042012
union all
Three things.
1. When you paste your code there into SSMS and execute, what happens? It fails due to numerous errors. Here's a fixed version if anyone wants to help you: -
CREATE TABLE PRODUCTS (
Product_id INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(10) NULL,
ProductType VARCHAR(10) NULL,
cost_price FLOAT NULL);
CREATE TABLE SALES (
SalesOrder_id INT CONSTRAINT pk_Orders_sid PRIMARY KEY,
Product_id INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(Product_id),
quantity INT NULL,
sales_price FLOAT NULL,
saledate DATETIME NULL);
INSERT INTO Products (product_id, productname, producttype, cost_price)
SELECT product_id, productname, producttype, cost_price
FROM (VALUES(1, 'tomato', 'veg', 55),
(2, 'apple', 'fruit', 40),
(3, 'peas', 'veg', 25),
(4, 'orange', 'fruit', 30),
(5, 'peach', 'fruit', 35)
)a(product_id, productname, producttype, cost_price);
INSERT INTO Sales (SalesOrder_id, product_id, quantity, sales_price, saledate)
SELECT SalesOrder_id, product_id, quantity, sales_price, saledate
FROM (VALUES(1,4, 10, 45, '2012-05-03'),
(2,4, 5, 45, '2012-04-08'),
(3,2, 6, 50, '2012-03-15'),
(4,2, 3, 50, '2012-02-08'),
(5,1, 10, 58, '2012-01-29'),
(6,1, 10, 58, '2012-04-24')
)a(SalesOrder_id, product_id, quantity, sales_price, saledate);
2. You have no sample data there that can be grouped, as the aggregates by the year/month/week/day will all be the same (either 0 or the total). This is because you've not provided enough sample data. Please add more and include expected results based on your sample data.
3. As Drew has mentioned, SQL isn't really designed for this sort of task. You can do it in SQL and we'll happily help you to, but for performance you should look at SSAS.
May 9, 2012 at 8:54 am
I think you may end up with some type of union no matter what but here is my solution...Change DENSE_RANK to RANK if you need to.
DECLARE @PRODUCTS TABLE (
Product_id int
,ProductName varchar(10) null
,ProductType varchar(10) null
,cost_price float null
)
DECLARE @SALES TABLE(
SalesOrder_id int,
Product_id INT,
quantity int null,
sales_price float null,
saledate datetime null
)
insert into @Products (product_id, productname,producttype,cost_price)
select 1, 'tomato', 'veg', 55
union all
select 2, 'apple', 'fruit', 40
union all
select 3, 'peas', 'veg', 25
union all
select 4, 'orange', 'fruit', 30
union all
select 5, 'peach', 'fruit', 35
insert into @Sales (SalesOrder_id,product_id, quantity,sales_price,saledate)
select 1,4, 10, 45, '05-03-2012'
UNION ALL
select 1,4, 7, 50, '05-03-2012'
UNION ALL
select 1,4, 8, 55, '05-03-2012'
UNION ALL
select 1,4, 9, 56, '05-03-2012'
UNION ALL
select 1,4, 10, 57, '05-03-2012'
UNION ALL
select 1,4, 11, 58, '05-03-2012'
UNION ALL
select 1,4, 11, 62, '05-11-2012'
union all
select 2,4, 5, 45, '04-08-2012'
union all
select 3,2, 6, 50, '03-15-2012'
union all
select 4,2, 3, 50, '02-08-2012'
union all
select 5,1, 10, 58, '01-29-2012'
union all
select 6,1, 10, 58, '04-24-2012'
;WITH CTE AS
(
SELECT
Yr = DATEPART(year,saledate),
Mnth = DATEPART(month,saledate),
Wk = DATEPART(week,saledate),
saledate,
productname,
Profit = (S.sales_price - P.Cost_Price) * S.Quantity,
YrRnk = DENSE_RANK() OVER (PARTITION BY DATEPART(year,saledate) ORDER BY (S.sales_price - P.Cost_Price) * S.Quantity DESC) ,
MnthRnk = DENSE_RANK() OVER (PARTITION BY DATEPART(year,saledate),DATEPART(month,saledate) ORDER BY (S.sales_price - P.Cost_Price) * S.Quantity DESC),
WkRnk = DENSE_RANK() OVER (PARTITION BY DATEPART(year,saledate),DATEPART(month,saledate),DATEPART(week,saledate) ORDER BY (S.sales_price - P.Cost_Price) * S.Quantity DESC),
DateRnk = DENSE_RANK() OVER (PARTITION BY saledate ORDER BY (S.sales_price - P.Cost_Price) * S.Quantity DESC)
FROM @PRODUCTS p INNER JOIN @SALES s
ON s.Product_id = p.Product_id
)
SELECT Grp = 'Year',Yr,Mnth,Wk,saledate,ProductName,Profit FROM CTE WHERE YrRnk <= 5
UNION ALL
SELECT 'Month',Yr,Mnth,Wk,saledate,ProductName,Profit FROM CTE WHERE MnthRnk <= 5
UNION ALL
SELECT 'Week',Yr,Mnth,Wk,saledate,ProductName,Profit FROM CTE WHERE WkRnk <= 5
UNION ALL
SELECT 'Day',Yr,Mnth,Wk,saledate,ProductName,Profit FROM CTE WHERE DateRnk <= 5
ORDER BY 1,2,3,4,5,7,6
**EDIT**
This is one query...maybe :unsure:
May 9, 2012 at 9:33 am
... complex OLAP is better done in a tool specifically designed to do OLAP such as SSAS...
So far OP failed to present enough details about required output to judge on which technology is more appropriate. If he wants cube, yes, it's OLAP and SSAS would be one of the tools to use.
However, if he just need flat output, T-SQL will handle it just as fine.
May 9, 2012 at 10:28 am
Eugene Elutin (5/9/2012)
... complex OLAP is better done in a tool specifically designed to do OLAP such as SSAS...
So far OP failed to present enough details about required output to judge on which technology is more appropriate. If he wants cube, yes, it's OLAP and SSAS would be one of the tools to use.
However, if he just need flat output, T-SQL will handle it just as fine.
I actually think that OP has provided enough information. They said that they need the top 5 products per year, month, week, day, and overall. In T-SQL, that would require five separate rankings: one for each level of detail. In an OLAP cube, it would only be one ranking evaluated in the current context.
If the OP were asking for simple counts or sums, it would be much less clear, because those functions are fully additive. TOP (n) queries are non-additive, so you can't base the results of a larger grouping on the results of smaller groupings. This non-additive nature of TOP (n) queries is the main reason that SSAS is much better suited for this than T-SQL.
Drew
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply