Need help with reusing the same calculation

PoseyRobert · June 14, 2017, 6:14pm

I have sql statement that is reusing the same calculation multiple times. This is making my statement very hard to maintain. The statement is building different aging bucket. This is NOT base on dates.

The first ## on each line is the line that is being calculated.

Here's my example : (number is the column name)

(number 10) Usage Amt = 100
(number 13) Total Inventory Qty = 121
(number 16) Inventory Total Amt = 200

(number 17) 0-30 burn off If(number 10 < number 16 then number 10 else number 16)

(number 18) 31-60 burn off If(number 10 + number 17) > number 16 then number 16 - number 17 else number 10)

(number 19) 61-90 burn off If(number 10 + number 17 + number 18) > number 16 then number 16 - number 17 - number 18 else number 10)

(number 20) 91-120 burn off If(number 10 + number 17 + number 18 + number 19) > number 16 then number 16 - number 17 - number 18 - number 19 else number 10)

(number 21) 121-150 burn off If(number 10 + number 17 + number 18 + number 19 + number 20) > number 16 then number 16 - number 17 - number 18 - number 19 - number 20 else number 10)

(number 22) 151-180 burn off If(number 10 + number 17 + number 18 + number 19 + number 20 + number 21) > number 16 then number 16 - number 17 - number 18 - number 19 - number 20 - number 21 else number 10)

(number 23) 181+ burn off number 13 – (number 10 + number 17 + number 18 + number 19 + number 20 + number 21 + number 22)

yosiasz · June 14, 2017, 7:16pm

what are these number x, are they a column name or actual values?

PoseyRobert · June 14, 2017, 7:17pm

Yes. The number is the "Column Name"

yosiasz · June 14, 2017, 7:19pm

how about the other column values?

PoseyRobert · June 14, 2017, 7:22pm

Yes. I change my example to make it a little more clear.

yosiasz · June 14, 2017, 7:58pm

there could be much better way but here is a sample of things

create table #sales(UsageAmt int, TotalInventoryQty int, InventoryTotalAmt int);
create table #bucketdefinitions(bucketgroupid int primary key, 
bucketgroup varchar(50), valueAmt int, ageFrom int , ageTo int );

insert into #sales
select 100, 121, 200

insert into #bucketdefinitions(bucketgroupid, bucketgroup, valueAmt, ageFrom, ageTo)
select 1 bucketgroupid,
        'Bucket 1' as bucketgroup,
		case
		when UsageAmt < InventoryTotalAmt 
               then UsageAmt
		else InventoryTotalAmt
		end as valueAmt,
		0 burnoff_from,
		30 burnoff_to
	From #sales;

--
insert into #bucketdefinitions(bucketgroupid, bucketgroup, valueAmt, ageFrom, ageTo)
select 2 bucketgroupid,
       'Bucket 2' as bucketgroup,
		case
		when (s.UsageAmt + bd.valueAmt) >  InventoryTotalAmt 
          then (InventoryTotalAmt - valueAmt) 
		else UsageAmt
		end as valueAmt,
		31 burnoff_from,
		60 burnoff_to
	From #bucketdefinitions bd
	cross apply #sales s
	where bucketgroupid = 1

select * from #bucketdefinitions


drop table #sales
drop table #bucketdefinitions

yosiasz · June 14, 2017, 8:11pm

but there seems like something is missing. which part are you wanting to re use? each calculation is so different from the other.

PoseyRobert · June 14, 2017, 8:34pm

That correct but the only different is that I have to kept amending the previous calculation to the next calculation.

yosiasz · June 15, 2017, 12:48am

the next calculation depends on values from previous calculation. I am not sure what you are looking for? can you expound?

Kristen · June 15, 2017, 12:59am

How about using multiple CROSS APPLY (or OUTER APPLY if you need calculations that might return zero rows)

Ifor · June 15, 2017, 11:13am

Without DDL, consumable test data and it's expected results, it is difficult to tell what you want.

Two possible options are multiple CROSS APPLYs, as suggested by Kristen, and recursion followed by a PIVOT.

With the following test data:

CREATE TABLE #t
(
	UseageAmt int NOT NULL
	,TotalInvQty int NOT NULL
	,InvTotalAmt int NOT NULL
);
INSERT INTO #t
VALUES (100, 121, 200);
--select * from #t

CROSS APPLYs

SELECT *
	,TotalInvQty -
	(
		UseageAmt
		+ X1.[0-30 burn off]
		+ X2.[31-60 burn off]
		+ X3.[61-90 burn off]
		+ X4.[91-120 burn off]
		+ X5.[121-150 burn off]
		+ X6.[151-180 burn off]
	) AS [181+ burn off] 
FROM #t T
	CROSS APPLY (VALUES (CASE WHEN T.UseageAmt < T.InvTotalAmt THEN T.UseageAmt ELSE T.InvTotalAmt END)) X1 ([0-30 burn off])
	CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off]) > T.InvTotalAmt THEN T.InvTotalAmt - X1.[0-30 burn off] ELSE T.UseageAmt END)) X2 ([31-60 burn off])
	CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off]) > T.InvTotalAmt
							 THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off]
							 ELSE T.UseageAmt
						END)) X3 ([61-90 burn off])
	CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off]) > T.InvTotalAmt
							 THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off]
							 ELSE T.UseageAmt
						END)) X4 ([91-120 burn off])
	CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off] + X4.[91-120 burn off]) > T.InvTotalAmt
							 THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off] - X4.[91-120 burn off]
							 ELSE T.UseageAmt
						END)) X5 ([121-150 burn off])
	CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off] + X4.[91-120 burn off] + X5.[121-150 burn off]) > T.InvTotalAmt
							 THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off] - X4.[91-120 burn off] - X5.[121-150 burn off]
							 ELSE T.UseageAmt
						END)) X6 ([151-180 burn off]);

Recursion

WITH BurnOffs
AS
(
	SELECT B.UseageAmt, B.TotalInvQty, B.InvTotalAmt, 1 AS BLevel
		,X.Nbr
		,X.Nbr AS NBrTotal
	FROM #t B
		CROSS APPLY (VALUES (CASE WHEN B.UseageAmt < B.InvTotalAmt THEN B.UseageAmt ELSE B.InvTotalAmt END) ) X (Nbr)
	UNION ALL
	SELECT B.UseageAmt, B.TotalInvQty, B.InvTotalAmt
		,B.BLevel + 1
		,X.Nbr
		,B.NBrTotal + X.Nbr
	FROM BurnOffs B
		CROSS APPLY (VALUES (CASE WHEN B.UseageAmt + B.NBrTotal > B.InvTotalAmt THEN B.InvTotalAmt - B.NBrTotal ELSE B.UseageAmt END) ) X (Nbr)
	WHERE B.BLevel < 6

)
SELECT UseageAmt, TotalInvQty, InvTotalAmt
	,[1] AS [0-30 burn off]
	,[2] AS [31-60 burn off]
	,[3] AS [61-90 burn off]
	,[4] AS [91-120 burn off]
	,[5] AS [121-150 burn off]
	,[6] AS [151-180 burn off]
	,TotalInvQty - (UseageAmt + [1] + [2] + [3] + [4] + [5] + [6]) AS [181+ burn off] 
FROM
(
	SELECT UseageAmt, TotalInvQty, InvTotalAmt, BLevel, Nbr
	FROM BurnOffs
) S
PIVOT
(
	MAX(Nbr)
	FOR BLevel IN ([1], [2], [3], [4], [5], [6])
) P;

The estimated plans suggest the CROSS APPLYs are more efficient but you should check your actual problem.

Jason_A_Long · June 15, 2017, 12:56pm

Use the "CROSS APPLY VALUES" method... Something along these lines...

IF OBJECT_ID('tempdb..#Numbers', 'U') IS NOT NULL 
DROP TABLE #Numbers;

CREATE TABLE #Numbers (
	Num10 INT NOT NULL,
	Num13 INT NOT NULL,
	Num16 INT NOT NULL 
	);
INSERT #Numbers (Num10, Num13, Num16) VALUES (100, 121, 200);

--===========================================================

SELECT 
	*
FROM
	#Numbers n
	CROSS APPLY ( VALUES (IIF(n.Num10 < n.Num13, n.Num10, n.Num13)) ) n17 (Num17)
	CROSS APPLY ( VALUES (IIF(n.Num10 + n17.Num17 > n.Num16, n.Num16 - n17.Num17, n.Num10)) ) n18 (num18)
	CROSS APPLY ( VALUES (IIF(n.Num10 + n17.Num17 + n18.num18 > n.Num16, n.Num16 - n17.Num17 - n18.num18, n.Num10)) ) n19 (Num19)
	CROSS APPLY ( VALUES (IIF(n.Num10 + n17.Num17 + n18.num18 + n19.Num19 > n.Num16, n.Num16 - n17.Num17 - n18.num18 - n19.Num19, n.Num10)) ) n20 (Num20)
	-- continue as necessary

PoseyRobert · June 15, 2017, 3:09pm

Hi Ifor,

I was able to get the CROSS APPLY to work. It seem a little simpler to use.

I did not have any luck getting the Recursion to work. But this 1 require a lot of thinking.

Thanks very much for taking the time to help me.

PoseyRobert · June 15, 2017, 3:17pm

Hi Jason_A_Long,

I am stilling playing around with your example. I notice you are using "IIF". This does not work in T-SQL.

I am working on changing this to a "CASE"

SELECT
*
FROM
#Numbers n
CROSS APPLY ( VALUES (CASE WHEN n.Num10 < n.Num13 THEN n.Num10 ELSE n.Num13)) n17 (Num17)

Jason_A_Long · June 15, 2017, 3:24pm

That's my fault... I failed to notice that you had indicated that you were using 2008... The IIF function wasn't added until 2012. Sorry about that.

In T-SQL, IIF is just a re-wrapped CASE expression, that allows for a more simplified syntax.

You are correct in that you can use CASE expressions in their place.

yosiasz · June 15, 2017, 3:29pm

also OP I think wants a way to bucketize things. Since we do not have enough sample data, it's just a guess but I guess the #numbers value is just for one product? there could be more products with different Num10, Num13, Num16 values then the calculation would bucketize different products into different buckets.

We did this for a manufacturing company that needed to bucketize titanium and aluminum plates by age. red = rusting, yellow=usable, green=brand new.

So besides doing the calculations it should also put products in buckets? just a guess

PoseyRobert · June 19, 2017, 9:14pm

Thanks ifor and Jason_A_Long for your help. Your example were great!

Got it working.

IF OBJECT_ID('tempdb..#Numbers', 'U') IS NOT NULL
DROP TABLE #Numbers;

CREATE TABLE #Numbers (
Num10 INT NOT NULL,
Num13 INT NOT NULL,
Num16 INT NOT NULL
);
INSERT #Numbers (Num10, Num13, Num16) VALUES (100, 121, 1250);

--===========================================================

SELECT A.Num10, A.Num13, A.Num16, B.[0-30],
C.[31-60], D.[61-90], E.[91-120], F.[121-150], G.[151-180], H.[181+]

FROM #Numbers AS A

CROSS APPLY (SELECT [0-30] = CASE WHEN A.Num10 < A.Num16 THEN a.Num10 ELSE a.Num16 END) AS B
CROSS APPLY (SELECT [31-60] = CASE WHEN A.Num10 + B.[0-30] > A.Num16 THEN a.Num16 - B.[0-30] ELSE a.Num10 END) AS C
CROSS APPLY (SELECT [61-90] = CASE WHEN A.Num10 + B.[0-30] + C.[31-60] > A.Num16 THEN a.Num16 - B.[0-30] - C.[31-60] ELSE a.Num10 END) AS D
CROSS APPLY (SELECT [91-120] = CASE WHEN A.Num10 + B.[0-30] + C.[31-60] + D.[61-90] > A.Num16 THEN a.Num16 - B.[0-30] - C.[31-60] - D.[61-90] ELSE a.Num10 END) AS E
CROSS APPLY (SELECT [121-150] = CASE WHEN A.Num10 + B.[0-30] + C.[31-60] + D.[61-90] + E.[91-120] > A.Num16 THEN a.Num16 - B.[0-30] - C.[31-60] - D.[61-90] - E.[91-120] ELSE a.Num10 END) AS F
CROSS APPLY (SELECT [151-180] = CASE WHEN A.Num10 + B.[0-30] + C.[31-60] + D.[61-90] + E.[91-120] + F.[121-150] > A.Num16 THEN a.Num16 - B.[0-30] - C.[31-60] - D.[61-90] - E.[91-120] - F.[121-150] ELSE a.Num10 END) AS G
CROSS APPLY (SELECT [181+] = CASE WHEN A.Num16 - (B.[0-30] + C.[31-60] + D.[61-90] + E.[91-120] + F.[121-150] + G.[151-180]) < 0 THEN 0 ELSE A.Num16 - (B.[0-30] + C.[31-60] + D.[61-90] + E.[91-120] + F.[121-150] + G.[151-180]) END) AS H

Jason_A_Long · June 20, 2017, 1:03am

Nice! Glad to see you got it working.