SQLTeam.com | Weblogs | Forums

Is this Calculation Possible in SQL?


#1

Hi,

I want to know whether is this possible in SQL?

I need to create "Calculation_Required" as a calculation for data below showcased:

week Store_id Item_id Promo_id Quantity Calculation_Required
20 101 355 22501 7 "=7/7+3"
20 101 355 0 3 0
21 101 356 0 5 0
21 101 356 35621 10 "=10/5+10+4"
22 101 356 42016 4 "=4/5+10+4"
23 101 358 96421 9 "=9/9+4"
23 101 358 0 4 0
25 101 359 25694 6 "=6/6+12"
25 101 359 0 12 0
26 101 360 36992 5 "=5/5+8"
26 101 360 0 8 0

Note:
If Promo_id is "0" then Calculation_Required is also "0".
If Promo_id is having value (Ex:22501) then calculation_Required is calculated as above shown table.

Thanks,
Ganesh K


#2

Not sure how many calculations there are or what the calculations mean (are they using data available?)?

Something like this would work though:

DECLARE @Test TABLE
(
	Week INT NOT NULL,
	Store_id int not null,
	item_id int not null,
	promo_id int not null,
	Quantity int not null,
	calculation_required int null
)

INSERT INTO @Test (Week,Store_id,item_id,promo_id,Quantity)
VALUES (20,101,355,22501,7),
(20,101,355,0,3),
(21,101,356,0,5),
(21,101,356,35621,10),
(22,101,356,42016,4),
(23,101,358,96421,9),
(23,101,358,0,4),
(25,101,359,25694,6),
(25,101,359,0,12),
(26,101,360,36992,5),
(26,101,360,0,8);

SELECT	T.Week,
		T.Store_id,
		T.item_id,
		T.promo_id,
		T.Quantity,
		CalcReq = CASE
			WHEN T.promo_id = 0 THEN 0
			WHEN T.promo_id = 22501 THEN 7 / 7 + 3
			ELSE NULL
		END		
FROM	@Test AS T

#3

Hi Dohsan,

SQL table "Test" which you have created is correct,

But Calculation_Required which is based on Quantity of same week, same store_id, Same Item_id.

To Breif:set1
week Store_id Item_id Promo_id Quantity Calculation_Required
20 101 355 22501 7 "=7/7+3"
20 101 355 0 3 0
In this if you observe all week, store_id, item_id are same but volume and Promo_id are different
So based on promo_id need to calculate (Calcuation_Required) with criteria as i mentioned earlier.
(If Promo_id is "0" then Calculation_Required is also "0".
If Promo_id is having value (Ex:22501) then calculation_Required is calculated as above shown table.)

In above ex:Promo_id has 2 values ("22501" and "0") which in term as "7" and "3" as Quantity, So i need to calculate for Promo_id with values(ex:22501) but not for "0" Promo_id, my Calculation would be

  1. 7/7+3 = 0.7 (i.e. Quantity of 22501 promo_id /Quantity of 22501 promo_id+Quantity of 0 promo_id)

Set 2:
21 101 356 0 5 0
21 101 356 35621 10 "=10/5+10+4"
22 101 356 42016 4 "=4/5+10+4"
In this if you observe all week, store_id, item_id are same but volume and Promo_id are different
In above ex:Promo_id has 3 values ("0", "35621" and "42016" ) which in term as "5", "10" and "4" as Quantity, So i need to calculate for Promo_id with values(ex:"35621" and "42016") but not for "0" Promo_id, my Calculation would be

  1. 10/5+10+4=0.526 (i.e. Quantity of 35621 promo_id /Quantity of 0 promo_id+Quantity of 35621 promo_id+Quantity of 42016 promo_id)
  2. 4/5+10+4=0.210 (i.e. Quantity of 42016 promo_id /Quantity of 0 promo_id+Quantity of 35621 promo_id+Quantity of 42016 promo_id)

Is this possible? Any Clarification required, please let me know.

Thanks,
Ganesh K


#4

So 0 is promo is 0 and if it has a promo value you wish to take the quantity for that promo and divide it by the total for that week/store/itemid

This should do that

WITH Base
AS
(
SELECT	T.Week,
		T.Store_id,
		T.item_id,
		T.promo_id,
		T.Quantity,
		TotalSum = SUM(T.Quantity) OVER (PARTITION BY T.Week,T.Store_id,T.Item_ID)
FROM	@Test AS T
)
SELECT	B.Week,
		B.Store_id,
		B.item_id,
		B.promo_id,
		B.Quantity,
		B.TotalSum,
		CalcReq = CASE
			WHEN B.promo_id = 0 THEN 0
			ELSE CAST(B.Quantity AS DECIMAL(18,5)) / B.TotalSum
		END
FROM	Base AS B;

#5
SELECT	*,
	calculation_required	= case 	when promo_id <> 0
					then Quantity * 1.0 / sum(Quantity) over(partition by item_id)
					else 0
					end
					
from	@Test