# Minimum volume logic

Hello,
Im currently having an issue, i dont know how or what method should I use.
The requirement, there's a minimum volume required for each reporting month. And if that volume requirement does not met, the volume would be added into the next month until the minimum volume has met.

Example:

Year Month Value Col MinVol Adjusted File Met
2018 1 30 A 100 30 1 0
2018 1 40 B 100 40 1 0
2018 2 15 A 100 45 2 1
2018 2 40 B 100 40 1 1
2018 2 20 C 100 20 2 1

In this example, the Month = 2 AND Col B has replicated from Month 1 since it was not existing in Month = 2.

So Month 1 Total = 70, since it did not meet the minimun volume 100, The Col A and B of Month = 1 would be added in the next month in two scenarios. If Col is existing then it will add the Value. If Col is not existing then it will be replicated. So the Month 2 Total = 105 and it met the minimum requirement.

Hope you're able to help me with this.

Thanks!
Jake

Hi Jnarciso23,

``````CREATE TABLE dbo.ValueChecking

(
Year_ numeric NOT NULL,
Month_ numeric NOT NULL,
Value_ numeric,
Col_ varchar(1) NOT NULL,
MinVol_ numeric,
File_ numeric,
Met_ numeric
PRIMARY KEY (Year_, Month_, Col_)
);

CREATE INDEX idxqq ON dbo.ValueChecking(Year_, Month_, Col_);

-----------------------------------------

INSERT INTO dbo.ValueChecking
VALUES(2018, 1, 30, 'A', 100, 30, 1, 0);

INSERT INTO dbo.ValueChecking
VALUES(2018, 1, 40, 'B', 100, 40, 1, 0);

INSERT INTO dbo.ValueChecking
VALUES(2018, 2, 15, 'A', 100, 15, 2, 0);

INSERT INTO dbo.ValueChecking
VALUES(2018, 2, 20, 'C', 100, 20, 2, 0);

select *
from dbo.ValueChecking;

-----------------------------------------
/*update process from source to next month*/

DECLARE
@YearSource numeric,
@MonthSource numeric,
@YearNext numeric,
@MonthNext numeric;

/*PARAMETERS*/
SET @YearSource = 2018;
SET @MonthSource = 1;

/*0. next period calculation*/
SET @YearNext =
CAST(@YearSource AS VARCHAR),
'-',
RIGHT('00' + CAST(@MonthSource AS VARCHAR), 2),
'-01') AS DATETIME)));
SET @MonthNext =
CAST(@YearSource AS VARCHAR),
'-',
RIGHT('00' + CAST(@MonthSource AS VARCHAR), 2),
'-01') AS DATETIME)));

select @YearSource, @MonthSource, @YearNext, @MonthNext;

/*1. copy data from the source period - if exists*/
UPDATE next_
SET next_.Adjusted_ = next_.Value_ + source_.Value_
FROM dbo.ValueChecking next_
INNER JOIN dbo.ValueChecking source_ ON source_.Year_ = @YearSource
AND source_.Month_ = @MonthSource
AND source_.Col_ = next_.Col_
WHERE next_.Year_ = @YearNext
AND next_.Month_ = @MonthNext;

select * from dbo.ValueChecking;

/*2. copy data from the source period - if not exists*/
INSERT INTO dbo.ValueChecking
SELECT
@YearNext,
@MonthNext,
source_.Value_,
source_.Col_,
source_.MinVol_,
source_.File_,
source_.Met_
FROM dbo.ValueChecking source_
LEFT JOIN dbo.ValueChecking next_ ON next_.Year_ = @YearNext
AND next_.Month_ = @MonthNext
AND next_.Col_ = source_.Col_
WHERE source_.Year_ = @YearSource
AND source_.Month_ = @MonthSource
AND next_.Col_ IS NULL;

select * from dbo.ValueChecking;

/*3. conditional checking/updating minimum criteria */
UPDATE dbo.ValueChecking
SET Met_ = 1
WHERE Year_ = @YearNext
AND Month_ = @MonthNext
AND (SELECT SUM(qq.Adjusted_) FROM dbo.ValueChecking qq WHERE qq.Year_ = @YearNext AND qq.Month_ = @MonthNext) >=

MinVol_;

select * from dbo.ValueChecking;
``````

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Hi

I took a stab at this

Please let me know what you think

My SQL Query
``````SELECT a.*,
CASE
WHEN asum < 100 THEN bsum + asum
END AS OK
FROM   (SELECT month123,
Sum(value123) AS Asum
FROM   valuechecking
GROUP  BY month123) a
LEFT JOIN (SELECT month123,
Sum(value123) AS Bsum
FROM   valuechecking
GROUP  BY month123) b
ON a.month123 - 1 = b.month123
``````

Hi

I am getting the result set

Thanks gigawatt38!

I believe this is for one time run.
How could I set to loop per row or to reoccur?
Specifically the parameters
SET @YearSource = 2018
SET @MonthSource = 1

How would I loop it with different months for each row? Im having issue on If not exist part since my source have different months but the parameter was set to only 1 month source.

I appreciated your help on this.

Hi harishgg1!

Thank you for your response, however i believe this would not work if you have a specific column where you could the Not Met volume.

I appreciated your time for helping me out on this.

Jake

Hi

however i believe this would not work if you have a specific column where you could the Not Met volume.

Does the below solution WORK for you JAKE

My create data script

My Create Data Script
``````drop table dbo.ValueChecking
go

CREATE TABLE dbo.ValueChecking

(
Year123 numeric NOT NULL,
Month123 numeric NOT NULL,
Value123 numeric,
Col123 varchar(1) NOT NULL,
MinVol123 numeric,
File123 numeric,
Met123 numeric
);

INSERT INTO dbo.ValueChecking
VALUES(2018, 1, 30, 'A', 100, 30, 1, 0);

INSERT INTO dbo.ValueChecking
VALUES(2018, 1, 40, 'B', 100, 40, 1, 0);

INSERT INTO dbo.ValueChecking
VALUES(2018, 2, 15, 'A', 100, 15, 2, 0);

INSERT INTO dbo.ValueChecking
VALUES (2018, 2, 40, 'B', 100, 40, 1, 1);

INSERT INTO dbo.ValueChecking
VALUES(2018, 2, 20, 'C', 100, 20, 2, 0);

select *
from dbo.ValueChecking;
``````
My Solution SQL Script
``````--select * from ValueChecking
SELECT
Amonth,sum(case when CSum < 100 then Avalue + isnull(Bvalue,0)end) as Asum123
from
(
SELECT a.Month123 as Amonth,A.Col123 as ACol,A.Value123 as Avalue,
b.Month123 as Bmonth,b.Col123 as BCol,b.Value123 as Bvalue,
c.CSum
FROM ValueChecking a Full OUTER JOIN ValueChecking b
ON a.Month123=b.Month123+1 and a.Col123 = b.Col123
join
(
SELECT month123,SUM(Value123) as CSum from ValueChecking group by Month123 ) c
on a.Month123-1 = c.Month123
) xx
group by Amonth
``````

Hi harishgg1,

But on your query, it totals all the value regardless of months.
Where it should be distributed for each month and will be added to next month if it doesn't met then min vol.

Would you happen to know the answer to my question to gigawatt38 above? The code he gave is working, it's just that i dont know how will I make the parameters set dynamic.

Hi Jake

Also I think cursor will help

https://www.dotnettricks.com/learn/sqlserver/sql-server-basics-of-cursors

Try this...

``````if OBJECT_ID('tempDb.dbo.#ValueChecking') is not null  drop table dbo.#ValueChecking;

CREATE TABLE #ValueChecking

(
[Year] numeric NOT NULL,
[Month] numeric NOT NULL,
Value numeric,
Col varchar(1) NOT NULL,
MinVol numeric,
[File] numeric,
Met numeric
PRIMARY KEY ([Year], [Month], Col)
);

INSERT INTO #ValueChecking
VALUES(2018, 1, 30, 'A', 100, 30, 1, 0);

INSERT INTO #ValueChecking
VALUES(2018, 1, 40, 'B', 100, 40, 1, 0);

INSERT INTO #ValueChecking
VALUES(2018, 2, 15, 'A', 100, 15, 2, 0);

INSERT INTO #ValueChecking
VALUES(2018, 2, 20, 'C', 100, 20, 2, 0);

--INSERT INTO #ValueChecking
--VALUES(2018, 3, 8, 'C', 100, 8, 3, 0);

--INSERT INTO #ValueChecking
--VALUES(2018, 3, 20, 'D', 100, 20, 3, 0);

as
(
select a.Year , a.Month ,    a.Col ,    b.Value [ToAdd]
from #ValueChecking a
,#ValueChecking b
where DATEADD(MONTH, 12*(b.Year - 1900)+ b.month-1, 0) = DATEADD(MONTH, 12*(a.Year - 1900)+ a.month-2, 0)
and b.Col = a.Col
)

, base
as
(

select a.[Year]
,a.[Month]
,a.Value
,a.Col
,a.MinVol
,case when  a.monthTotal < a.MinVol  then a.Adjusted  +

isnull(
(
where b.Year = a.Year
and b.Month = a.Month
and b.Col = a.Col
), 0)

,a.[File]
,a.Met
,
case when  a.monthTotal < a.MinVol then

a.monthTotal +
isnull(
(
where b.Year = a.Year
and b.Month = a.Month
), 0)

else a.monthTotal
end
[monthTotal]

,cast(a.dt as DATE) [dt]

from
(
select a.*, SUM(a.value) over(partition by a.month order by a.month) [monthTotal] , DATEADD(MONTH, 12*(a.Year - 1900)+ a.month-1, 0) [dt]
from #ValueChecking a
) a

)

,cte
as
(

select * from base

union all

select
b.Year
,b.Month
,a.Value
,a.Col
,a.MinVol
,a.[File]
,a.Met
,b.monthTotal
,b.dt

from cte a
, base b

and not exists(

select 1
from base d
where d.Year = b.Year
and d.Month = b.Month
and d.Col = a.Col
)

and a.monthTotal < a.MinVol

)

select distinct
a.Year
,a.Month
,a.Value
,a.Col
,a.MinVol