SQL Syntax: Create table with calculated category

HKFuey
HKFuey used Ask the Experts™
on
I have this view with the year to date sales values for each stock item sold: -
SELECT        Year, [Stock Code], SUM(SalesValue) AS TotVal
FROM            dbo.tbl_My_Table
GROUP BY Year, [Stock Code]
HAVING        (Year = YEAR(GETDATE()))

I want to create a SQL job to populate a table overnight e.g. tbl_Inventory_Category

I need to add StockCode and Category to the table, StockCode comes from the view. 'Category' is calculated thus:

•      A=  Products that make up 65% year to date sales.
•      B= Next 20%
•      C= Next 10%
•      D= Next 5%

Does anyone know how to achieve this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
"I need to add .... Category to the table" this is the only column you are seeking right?

Using RANK() or DENSE_RANK()  may achieve some of what you are asking for, but I'm not sure I follow completely.

Could you provide a sample of the data, and an expected result for that sample?
Ryan ChongSoftware Team Lead

Commented:
in which table and field you used to store the yearly target sales for each stock code?

Author

Commented:
I will try to explain the calculation I need: -
No targets, based on year to date sales value per product line.

I need to Calculate the total value for all Stock codes say it is £100k for example.
Each product's total sales make a portion of the £100k, those that make up the top 65% of Sales value need to be Category 'A'
Those in the bottom 5% would be category 'D'
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
A=  Products that make up 65% year to date sales.
does this give priority for the products that got a bigger Total Sales Value or sales quantity, etc?

for example, product A got a SalesValue of £30k with 1 sale Versus product B got a total SalesValue of £30k (£10 x 3 sales)

which one has the priority?
Software Team Lead
Commented:
see if this is something you need?

declare @tbl_My_Table table
(
	ID int,
	Year int,
	[Stock Code] varchar(20),
	SalesValue money
);

insert into @tbl_My_Table values
(1, 2016, 'ABC', 1000),
(2, 2017, 'ABC', 5000),
(3, 2017, 'BBB', 5000),
(4, 2017, 'CCC', 15000),
(5, 2017, 'ABC', 25000),
(6, 2017, 'GGG', 35000),
(7, 2017, 'EEE', 45000),
(8, 2017, 'FFF', 55000),
(9, 2017, 'BBB', 21600),
(10, 2017, 'FFF', 21900)

;with cte as
(
SELECT row_number() over (order by SUM(SalesValue) desc) idx,
Year, [Stock Code], SUM(SalesValue) AS TotVal
FROM @tbl_My_Table
GROUP BY Year, [Stock Code]
HAVING (Year = YEAR(GETDATE()))
)
select cte.*, c.TotValCumulative / b.total * 100.0 Pct, c.TotValCumulative,
case
	when c.TotValCumulative / b.total <= 0.65 then 'A'
	when c.TotValCumulative / b.total <= 0.85 then 'B'
	when c.TotValCumulative / b.total <= 0.95 then 'C'
	else 'D'
end Category	
from cte
cross apply (select sum(SalesValue) as total from @tbl_My_Table t where Year = YEAR(GETDATE())) b
cross apply (select sum(TotVal) as TotValCumulative from cte t where cte.idx >= t.idx and Year = YEAR(GETDATE())) c
order by TotVal desc

Open in new window

Author

Commented:
Hi Ryan,
That looks really promising, thanks for your time!

I have a lot on today so will have a look next week.

Andy

Author

Commented:
thanks fro trying to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial