Minimum volume logic

Transact-SQL
1

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

2

Hi Jnarciso23,

Please try my solution:

CREATE TABLE dbo.ValueChecking

(
  Year_ numeric NOT NULL,
  Month_ numeric NOT NULL,
  Value_ numeric,
  Col_ varchar(1) NOT NULL,
  MinVol_ numeric,
  Adjusted_ 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 =
  YEAR(DATEADD(mm, +1, CAST(CONCAT(
    CAST(@YearSource AS VARCHAR),
    '-',
    RIGHT('00' + CAST(@MonthSource AS VARCHAR), 2),
    '-01') AS DATETIME)));
SET @MonthNext =
  MONTH(DATEADD(mm, +1, CAST(CONCAT(
    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_.Adjusted_,
  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

Tested by: http://rextester.com/l/sql_server_online_compiler

5

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
6

Hi

I am getting the result set

image

7

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.

8

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

10

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,
  Adjusted123 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

image

11

Hi harishgg1,

Thanks again for your response.
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.

Thank you for your help.

12

Hi Jake

Will this link help
https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

13

Also I think cursor will help

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

14

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,
  Adjusted 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);

with ToAdd
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(
(
select b.ToAdd
from ToAdd b
where b.Year = a.Year 
and b.Month = a.Month
and b.Col = a.Col 
), 0)


else a.Adjusted end [Adjusted]

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


a.monthTotal +
isnull(
(
select b.ToAdd
from ToAdd b
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.Adjusted 
,a.[File] 
,a.Met
,b.monthTotal 
,b.dt

from cte a
, base b

where dateadd(month, 1,a.dt)  = b.dt 

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
,a.Adjusted
,a.[File]
,a.Met

from cte a 
order by Year, month, Col;

Hope this helps!