locked
create records based upon criteria RRS feed

  • 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

Answers

  • 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 Aaron Alton 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 Aaron Alton Sunday, March 1, 2009 11:32 PM
    Sunday, March 1, 2009 4:42 AM
  • Thanks very much!!!!
    Sunday, March 1, 2009 11:22 PM