# create records based upon criteria

• ### Question

• I need to develop a baseline estimate based upon these conditions and I need help creating the stored procedure.

I need to gather the last 6 months of history by customer for lbs shipped. The months 5-6 are the last 2 months and they will be used in a calculation.

rule1:

If months 5-6 total lbs greater than 0.75 of the total 1-6 months for this customer than just give me a place to add some logic

rule2:

If months 5-6 total lbs is less than 0.3 of the total 1-6 months for that customer... than again give me a place to insert logic.

rule3:

If month6 equal to 0... leave me a place for that logic.

rule4:

If months 1-4 is less than 100 .. give me a place to add more logic..

rule5:

If customer doesn't fall into above logic..  then I need a place to add logic here..

<><>

The table to get the 6 months of history data has Custnbr,Invoice,line,InvoiceDate,lbs fileds defined.

The records need to be created by customer using inserting them based upon rul1 thru rul5.

Thanks.
Sunday, February 22, 2009 2:54 PM

• Give this a whirl:

 CREATE TABLE Baseline ( CustNbr varchar(12) not null, BlMonth char(2) not null, BlYear  char(4) not null, Mtl     char(2) not null, Qty     decimal(18,4), BlRank  int ); insert into Baseline SELECT 1,'01','2009','a',100,1 UNION ALL SELECT 1,'12','2008','a',500,2 UNION ALL SELECT 1,'11','2008','a',50,3 UNION ALL SELECT 1,'10','2008','a',100,4 UNION ALL SELECT 1,'09','2008','a',200,5 UNION ALL SELECT 1,'08','2008','a',0,6 UNION ALL SELECT 2,'01','2009','b',0,1 UNION ALL SELECT 2,'12','2008','b',50,2 UNION ALL SELECT 2,'11','2008','b',25000,3 UNION ALL SELECT 2,'10','2008','b',100,4 UNION ALL SELECT 2,'09','2008','b',200,5 UNION ALL SELECT 2,'08','2008','b',0,6; ;WITH AggregatedBaseline AS (SELECT CustNbr, Mtl, SUM ( CASE WHEN BlRank IN (1,2) THEN Qty ELSE 0 END ) AS Ranks1And2, SUM ( CASE WHEN BlMonth IN ('01', '02', '03', '04', '05', '06') THEN Qty ELSE 0 END ) AS Months1To6, SUM ( CASE WHEN BlRank = 1 AND Qty = 0 THEN 1 ELSE 0 END ) AS Rank1Zero, SUM ( CASE WHEN BlMonth IN ('03', '04', '05', '06') THEN Qty ELSE 0 END ) AS Month3To6Qty, SUM(Qty) AS TotalSum, COUNT(Qty) AS NumberOfMonths FROM        Baseline GROUP BY    CustNbr, Mtl) SELECT      CustNbr, Mtl, CASE WHEN Ranks1And2 > (0.75 * Months1To6) OR Ranks1And2 < (0.3 * Months1To6) THEN (Ranks1And2/2.00)  --Rules 1 and 2 WHEN Rank1Zero = 1 THEN 0   --Rule 3 WHEN Month3To6Qty < 100 THEN 0 --Rule 4 ELSE TotalSum/NumberOfMonths  --Rule 5 END AS MagicNumber FROM        AggregatedBaseline

You can easily construct an insert from the query....

HTH!
Aaron Alton | thehobt.blogspot.com
• Marked as answer by Sunday, March 1, 2009 11:32 PM
Sunday, March 1, 2009 4:42 AM

### All replies

• OK - what do you have so far?  Any ideas?  Please post the CREATE scripts for the tables involved, as well as some sample data in the form of INSERT statements.
Aaron Alton | thehobt.blogspot.com
Sunday, February 22, 2009 4:32 PM
•  I have worked my way thru the procedure to have created a table that has customer data by month (past 6) and ranked in order  needed. WIth rank 1,2 being the last  months. Thats different from what I stated above.

Create Table Baseline(
CustNbr Varchar(12) not null,
Month Char(2) not null,
Year char(4) not null,
Mtl char(2) not null,
Qty Dec(18,4),
Rank int);

The custnbr,month,year,mtl  are used as the key to the table.

insert into Baseline
values (1,'01','2009','a',100,1);

insert into Baseline
values (1,'12','2008','a',500,2);

insert into Baseline
values (1,'11','2008','a',50,3);

insert into Baseline
values (1,'10','2008','a',100,4);

insert into Baseline
values (1,'09','2008','a',200,5);

insert into Baseline
values (1,'08','2008','a',0,6);

insert into Baseline
values (2,'01','2009','b',0,1);

insert into Baseline
values (2,'12','2008','b',50,2);

insert into Baseline
values (2,'11','2008','b',25000,3);

insert into Baseline
values (2,'10','2008','b',100,4);

insert into Baseline
values (2,'09','2008','b',200,5);

insert into Baseline
values (2,'08','2008','b',0,6);

Now I need to build a new record based upon the rules using the table defined above with data from inserts. I just inserted a few records but if the logic creates these records it should work for true workload.

RULE1:

If rank 1-2 total qty greater than 0.75 of the total months 1-6 for this customer and mtl.

Then avg(rank1,rank2) and use that number to build a new record in a table that will be used to plan for the next 18 months.

Insert into new table:

CustNbr
Month
Year
Mtl
CM ( This is the value calculated above)
CM2 (same value as CM)
....
CM18(same value as CM)

Rule2:

If ranks 1-2 total qty is less than 0.3 of the total 1-6 months for that customer and mtl.

Then avg(rank1,rank2) and use that number to build a new record in a table that will be used to plan for the next 18 months.

Insert into new table:

CustNbr
Month
Year
Mtl
CM ( This is the value calculated above)
CM2 (same value as CM)
....
CM18(same value as CM)

Rule3:

If rank 1 qty is 0 for that customer and mtl.

Then populate 0 in the table that will be used to plan for the next 18 months.

Insert into new table:

CustNbr
Month
Year
Mtl
CM  0
CM2 (same value as CM)
....
CM18 0

Rule4:

If rank 3-6 value for the customer and mtl is less than 100

Then populate 0 in the table that will be used to plan for the next 18 months.

Insert into new table:

CustNbr
Month
Year
Mtl
CM  0
CM2 (same value as CM)
....
CM18 0

Rule5:

Else

If customer and mtl doesn't meet the above criteria.

avg(rank1,rnk2,rnk3,rnk4,rnk5.rnk6)

Then use that value to populate the table that will be used to plan for the next 18 months.

Insert into new table:

CustNbr
Month
Year
Mtl
CM  avg(rank1,rnk2,rnk3,rnk4,rnk5.rnk6)
CM2 (same value as CM)
....
CM18 avg(rank1,rnk2,rnk3,rnk4,rnk5.rnk6)

This is where I need help processing records from Basline and populating the new table using the rules above. Do I use a case statement,CTE,

just not sure how to get the new table populated.

Thanks again.

Thursday, February 26, 2009 1:36 AM
• When the new record is built. The value for month will always be Current month and Year the same thing Current Year.

The record added to the table will be

Custnbr - 1
Month - 02
Year - 2009
Mtl - A
CM - Has the new value from calc above

CM18 -- Has the new value from calc and same as CM

Thursday, February 26, 2009 11:53 AM
•  any sugsestions on the code that can make this happen?
Saturday, February 28, 2009 1:44 PM
• Give this a whirl:

 CREATE TABLE Baseline ( CustNbr varchar(12) not null, BlMonth char(2) not null, BlYear  char(4) not null, Mtl     char(2) not null, Qty     decimal(18,4), BlRank  int ); insert into Baseline SELECT 1,'01','2009','a',100,1 UNION ALL SELECT 1,'12','2008','a',500,2 UNION ALL SELECT 1,'11','2008','a',50,3 UNION ALL SELECT 1,'10','2008','a',100,4 UNION ALL SELECT 1,'09','2008','a',200,5 UNION ALL SELECT 1,'08','2008','a',0,6 UNION ALL SELECT 2,'01','2009','b',0,1 UNION ALL SELECT 2,'12','2008','b',50,2 UNION ALL SELECT 2,'11','2008','b',25000,3 UNION ALL SELECT 2,'10','2008','b',100,4 UNION ALL SELECT 2,'09','2008','b',200,5 UNION ALL SELECT 2,'08','2008','b',0,6; ;WITH AggregatedBaseline AS (SELECT CustNbr, Mtl, SUM ( CASE WHEN BlRank IN (1,2) THEN Qty ELSE 0 END ) AS Ranks1And2, SUM ( CASE WHEN BlMonth IN ('01', '02', '03', '04', '05', '06') THEN Qty ELSE 0 END ) AS Months1To6, SUM ( CASE WHEN BlRank = 1 AND Qty = 0 THEN 1 ELSE 0 END ) AS Rank1Zero, SUM ( CASE WHEN BlMonth IN ('03', '04', '05', '06') THEN Qty ELSE 0 END ) AS Month3To6Qty, SUM(Qty) AS TotalSum, COUNT(Qty) AS NumberOfMonths FROM        Baseline GROUP BY    CustNbr, Mtl) SELECT      CustNbr, Mtl, CASE WHEN Ranks1And2 > (0.75 * Months1To6) OR Ranks1And2 < (0.3 * Months1To6) THEN (Ranks1And2/2.00)  --Rules 1 and 2 WHEN Rank1Zero = 1 THEN 0   --Rule 3 WHEN Month3To6Qty < 100 THEN 0 --Rule 4 ELSE TotalSum/NumberOfMonths  --Rule 5 END AS MagicNumber FROM        AggregatedBaseline

You can easily construct an insert from the query....

HTH!
Aaron Alton | thehobt.blogspot.com
• Marked as answer by Sunday, March 1, 2009 11:32 PM
Sunday, March 1, 2009 4:42 AM
• Thanks very much!!!!
Sunday, March 1, 2009 11:22 PM