This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Jan 4, 2013 12:58 PM by 978776 RSS

Merging two queries?

978776 Newbie
Currently Being Moderated
I have this query:
WITH  weeks_vw AS 
    ( SELECT  DISTINCT TO_CHAR(fill_year_day,'IYYYIW') AS fill_year_week 
            FROM 
           ( SELECT  to_date('2012-05-01','yyyy-mm-dd')  + (ROWNUM-1) AS fill_year_day 
                    FROM    DUAL 
                    CONNECT BY      LEVEL <= TRUNC(to_date('2012-06-30','yyyy-mm-dd') - to_date('2012-05-01','yyyy-mm-dd') ) + 1) 
    )
,   got_sales_qty_sum     AS
(
    SELECT  a.material, a.plant, sum(NVL(quantity,0)) AS sales_qty_sum 
    ,         AVG    (sum(NVL(quantity,0))) OVER (PARTITION BY  material)    AS material_avg
    ,         STDDEV (sum(NVL(quantity,0))) OVER (PARTITION BY  material)    AS material_stddev
    FROM
       (
         SELECT  t.material, t.plant, t.quantity, TO_CHAR(t.posting_date,'IYYYIW') AS posting_week 
         FROM    tblCon t 
         Where     t.plant = '1900' 
         AND     t.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
         AND     t.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
         AND     t.material = 'Label1' 
       ) A
                         PARTITION BY (a.material,a.plant) 
       RIGHT OUTER JOIN weeks_vw  ON weeks_vw.fill_year_week = A.posting_week 
       GROUP BY  a.material, a.plant, fill_year_week
)
SELECT     *
FROM     got_sales_qty_sum
WHERE     ABS (sales_qty_sum - material_avg)     <= 2.0 * material_stddev
;
Now I want to add this query as I have since previously. The query does not take relese and revisons changes into account . It sums the total quantity from earlier release and revisoner. But how do I get this functionality on first query?
WITH got_base_material AS
(
SELECT material, Plant, quantity, to_char(Posting_Date, 'IYYYIW') AS year_week
, REGEXP_REPLACE ( material
       , 'R..$'
       ) AS base_material
FROM tblcon

)
SELECT   FIRST_VALUE (MAX (material)) OVER ( PARTITION BY  base_material
      ORDER BY     year_week  DESC
    ) AS last_material
,   SUM (quantity) AS total_quantity
,   year_week
,   Plant
FROM   got_base_material
GROUP BY  base_material
,   year_week
,   Plant
;
I used REGEXP_REPLACE to extract material from release and revision.



Example on result from abowe query is. Here is data from the table before running the query. This shows per month, but i want it per day.
Material----|---Quantity---|--Period 
 
LABEL10R1A--|-----10-------|---201009 
LABEL10R1B--|-----20-------|---201019
LABEL10R1C--|-----30-------|---201010
LABEL13R1A--|-----10-------|---201006
LABEL13R2A--|-----10-------|---201008
LABEL11-----|-----15-------|---201005
LABEL12-----|-----25-------|---201006
It presents the latest revision material like this:
Material----|----Quantity--|--Period 
 
LABEL10R1C--|-----30-------|---201006 
LABEL10R1C--|-----30-------|---201010
LABEL13R2A--|-----10-------|---201006
LABEL13R2A--|-----10-------|---201008
LABEL11-----|-----15-------|---201005
LABEL12-----|-----25-------|---201006
  • 1. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Help...
  • 2. Re: Merging two queries?
    Boneist Guru
    Currently Being Moderated
    If you provided enough information for those of us who have no concept of what your tables look like, what your original data and requirements are, then perhaps we would be able to help. As it is, there isn't enough information to be able to help you.

    If we can't reproduce your results by running the query for ourselves, how are we meant to be able to help you?
  • 3. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    I must apologize if there was insufficient data. Hope you can help me now.

    create table tblCon  (
    material   varchar2(10),
    plant   varchar2(4),
    quantity number,
    posting_date date
    );
    
    
    insert into tblCon values('Label1R1A',1900,1000,to_date('2012-05-07','yyyy-mm-dd'));
    insert into tblCon values('Label1R1B',1900,184,to_date('2012-05-09','yyyy-mm-dd'));
    insert into tblCon values('Label1R1B',1900,570,to_date('2012-05-10','yyyy-mm-dd'));
    insert into tblCon values('Label1R1B',1900,770,to_date('2012-05-11','yyyy-mm-dd'));
    insert into tblCon values('Label1R1C',1900,888,to_date('2012-05-16','yyyy-mm-dd'));
    insert into tblCon values('Label1R1C',1900,651,to_date('2012-05-17','yyyy-mm-dd'));
    insert into tblCon values('Label1R1C',1900,1081,to_date('2012-05-18','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,1085,to_date('2012-05-19','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,3240,to_date('2012-05-20','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,9,to_date('2012-05-24','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,1165,to_date('2012-05-26','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,105,to_date('2012-05-27','yyyy-mm-dd'));
    insert into tblCon values('Label1R2A',1900,1165,to_date('2012-05-28','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,2125,to_date('2012-05-29','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,53211,to_date('2012-05-30','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,3240,to_date('2012-06-01','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,9,to_date('2012-06-03','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,1165,to_date('2012-06-16','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,10,to_date('2012-06-26','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,1165,to_date('2012-06-28','yyyy-mm-dd'));
    insert into tblCon values('Label1R2B',1900,2125,to_date('2012-06-29','yyyy-mm-dd'));
    insert into tblCon values('Label1R2C',1900,8334,to_date('2012-06-30','yyyy-mm-dd'));
    insert into tblCon values('Label2R1A',1900,1165,to_date('2012-06-16','yyyy-mm-dd'));
    insert into tblCon values('Label2R1A',1900,10,to_date('2012-06-26','yyyy-mm-dd'));
    insert into tblCon values('Label2R1A',1900,1165,to_date('2012-06-28','yyyy-mm-dd'));
    insert into tblCon values('Label2R1A',1900,2125,to_date('2012-06-29','yyyy-mm-dd'));
    insert into tblCon values('Label2R1B',1900,8334,to_date('2012-06-30','yyyy-mm-dd'));
    commit;
  • 4. Re: Merging two queries?
    Boneist Guru
    Currently Being Moderated
    and what did you want the output to look like, and what's the reasoning on how to get it?
  • 5. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    I want to use the analysis question to identify outliers. It shall also take account of past releases and revisions to the material, otherwise the history would be incorrect.
    There, that I want to merge them. Do you understand better now or do you want me to show one exemple of the expected output?
    I think it will be hard for me to get an output, given that I want to get with the outlier, which means that the result varies.
  • 6. Re: Merging two queries?
    Boneist Guru
    Currently Being Moderated
    570142 wrote:
    or do you want me to show one exemple of the expected output?
    In a word: yes!
    I think it will be hard for me to get an output, given that I want to get with the outlier, which means that the result varies.
    um, if you give one set of inputs to the query, surely you're going to get the same output, no matter how many times the query is run? In other words, what is the expected output for the input data you have already provided, and what's the logic behind how it was achieved?
  • 7. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    The output should be like this:
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier and this line will be removed** (WHERE     ABS (sales_qty_sum - material_avg)     <= 2.0 * material_stddev)
    Label1R2C     1900     1 165          1224
    Label1R2C     1900     11 634          1226
    Label2R1B     1900     1 165          1224
    Label2R1B     1900     11 634          1226
    I guess Frank Kulash know what I mean and maybe he also can help me...
  • 8. Re: Merging two queries?
    Boneist Guru
    Currently Being Moderated
    If Frank can work out what you're after, then I'll take off my hat to him! *{
    {noformat};-){noformat}

    So, I've taken your data and the first query from your original post, and I don't get anywhere near the output that you claim to be expecting:
    with        tblcon as (select 'Label1R1A' material, 1900 plant, 1000 quantity, to_date('2012-05-07','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1B' material, 1900 plant, 184 quantity, to_date('2012-05-09','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1B' material, 1900 plant, 570 quantity, to_date('2012-05-10','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1B' material, 1900 plant, 770 quantity, to_date('2012-05-11','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1C' material, 1900 plant, 888 quantity, to_date('2012-05-16','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1C' material, 1900 plant, 651 quantity, to_date('2012-05-17','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R1C' material, 1900 plant, 1081 quantity, to_date('2012-05-18','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 1085 quantity, to_date('2012-05-19','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 3240 quantity, to_date('2012-05-20','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 9 quantity, to_date('2012-05-24','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 1165 quantity, to_date('2012-05-26','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 105 quantity, to_date('2012-05-27','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2A' material, 1900 plant, 1165 quantity, to_date('2012-05-28','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 2125 quantity, to_date('2012-05-29','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 53211 quantity, to_date('2012-05-30','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 3240 quantity, to_date('2012-06-01','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 9 quantity, to_date('2012-06-03','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 1165 quantity, to_date('2012-06-16','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 10 quantity, to_date('2012-06-26','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 1165 quantity, to_date('2012-06-28','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2B' material, 1900 plant, 2125 quantity, to_date('2012-06-29','yyyy-mm-dd') posting_date from dual union all
                           select 'Label1R2C' material, 1900 plant, 8334 quantity, to_date('2012-06-30','yyyy-mm-dd') posting_date from dual union all
                           select 'Label2R1A' material, 1900 plant, 1165 quantity, to_date('2012-06-16','yyyy-mm-dd') posting_date from dual union all
                           select 'Label2R1A' material, 1900 plant, 10 quantity, to_date('2012-06-26','yyyy-mm-dd') posting_date from dual union all
                           select 'Label2R1A' material, 1900 plant, 1165 quantity, to_date('2012-06-28','yyyy-mm-dd') posting_date from dual union all
                           select 'Label2R1A' material, 1900 plant, 2125 quantity, to_date('2012-06-29','yyyy-mm-dd') posting_date from dual union all
                           select 'Label2R1B' material, 1900 plant, 8334 quantity, to_date('2012-06-30','yyyy-mm-dd') posting_date from dual),
              weeks_vw AS (SELECT TO_CHAR(TO_DATE('2012-05-01', 'yyyy-mm-dd') + (level - 1)*7, 'iyyyiw') fill_year_week
                                  FROM   DUAL
                                  CONNECT BY LEVEL <= (trunc(TO_DATE('2012-06-30', 'yyyy-mm-dd'), 'iw') - trunc(TO_DATE('2012-05-01', 'yyyy-mm-dd'), 'iw'))/7 + 1),
    got_sales_qty_sum  AS
    (
        SELECT  a.material, a.plant, sum(NVL(quantity,0)) AS sales_qty_sum, weeks_vw.fill_year_week
        ,      AVG    (sum(NVL(quantity,0))) OVER (PARTITION BY  material)    AS material_avg
        ,      STDDEV (sum(NVL(quantity,0))) OVER (PARTITION BY  material)    AS material_stddev
        FROM
           (
             SELECT  t.material, t.plant, t.quantity, TO_CHAR(t.posting_date,'IYYYIW') AS posting_week 
             FROM    tblCon t 
             Where     t.plant = '1900' 
             AND     t.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
             AND     t.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
    --         AND     t.material = 'Label1' 
           ) A
                    PARTITION BY (a.material,a.plant) 
           RIGHT OUTER JOIN weeks_vw  ON weeks_vw.fill_year_week = A.posting_week 
           GROUP BY  a.material, a.plant, fill_year_week
    )
    SELECT  *
    FROM    got_sales_qty_sum
    WHERE   ABS (sales_qty_sum - material_avg)  <= 2.0 * material_stddev
    and sales_qty_sum != 0
    order by material, fill_year_week;
    
    MATERIAL       PLANT SALES_QTY_SUM FILL_YEAR_WEEK MATERIAL_AVG MATERIAL_STDDEV
    --------- ---------- ------------- -------------- ------------ ---------------
    Label1R2A       1900          1279 201221           752.111111      1440.81795
    Label1R2A       1900          1165 201222           752.111111      1440.81795
    Label1R2B       1900          1165 201224           7005.55556      19373.7876
    Label1R2B       1900          3300 201226           7005.55556      19373.7876
    Label2R1A       1900          1165 201224           496.111111      1119.82638
    Perhaps you could explain further? Are you expecting to keep the rows with 0 sales_qty_sum?
  • 9. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Perhaps this is what you want:
    WITH      got_base_material     AS
    (
         SELECT  material
         ,     plant
         ,     quantity
         ,     TO_CHAR (Posting_Date, 'IYYYIW')     AS year_week
         ,      REGEXP_REPLACE ( material
                                 , 'R..$'
                                 )               AS base_material
         FROM    tblcon
     )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (material)) 
                            OVER ( PARTITION BY  base_material
                                      ORDER BY      year_week  DESC
                               )          AS last_material
         ,          SUM (quantity)     AS week_quantity
         ,          year_week
         ,          plant
         ,       AVG (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_avg
         ,       STDDEV (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_stddev
         FROM          got_base_material
         GROUP BY  base_material
         ,            year_week
         ,          plant
    )
    SELECT       last_material
    ,       week_quantity
    ,       year_week
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( week_quantity
               - week_quantity_avg
               )          < week_quantity_stddev
    ORDER BY  last_material
    ,            year_week
    ,          plant
    ;
    You'll notice that this is very similar to the 2nd query from your first message. I turned the original main query into the sub-quer got_aggregates, and added 2 more columns (week_quantity_avg and week_quantity_stddev). In the new main query, those 2 values are used to identify outliers, that is, weekly totals that are more than 2 standard deviations away from the average for the same base_material and plant.

    This gets the results you requested from the sample data. I can't guarantee that it will get the right results on any other set of data, because I don't understand what your requirements are.
  • 10. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi Frank,

    You've understand what I'm looking for. Thanks for the help.
    I'm thinking about where to put my criteria. I want to specify a date range, materials and plant criteria. And I also want to be able to adjust the level of outlier. Where do I put it in the best way?
    Like this?
    WITH      got_base_material     AS
    (
         SELECT  material
         ,     plant
         ,     quantity
         ,     TO_CHAR (Posting_Date, 'IYYYIW')     AS year_week
         ,      REGEXP_REPLACE ( material
                                 , 'R..$'
                                 )               AS base_material
         FROM    tblcon
    
                 Where     tblcon.plant = '1900' 
                 AND       tblcon.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
                  AND       tblcon.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
                  AND       tblcon.material IN('Label1R2C','Label2R1B')  '' *** It shall take all revisions and releases the material contained in the current period ***
     )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (material)) 
                            OVER ( PARTITION BY  base_material
                                      ORDER BY      year_week  DESC
                               )          AS last_material
         ,          SUM (quantity)     AS week_quantity
         ,          year_week
         ,          plant
         ,       AVG (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_avg
         ,       STDDEV (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_stddev
         FROM          got_base_material
         GROUP BY  base_material
         ,            year_week
         ,          plant
    )
    SELECT       last_material
    ,       week_quantity
    ,       year_week
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( week_quantity
               - week_quantity_avg
               )          <= 2.0 * week_quantity_stddev '' *** Is this right way to adjust the outlier? ***
    
    
    ORDER BY  last_material
    ,            year_week
    ,          plant
    ;
  • 11. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    ... I'm thinking about where to put my criteria. I want to specify a date range, materials and plant criteria. And I also want to be able to adjust the level of outlier. Where do I put it in the best way?
    It depends on what results you want, and why. Get in the habit of posting your desired results, and explaining why you want those results given the sample data.
    Like this?
    WITH      got_base_material     AS
    (
         SELECT  material
         ,     plant
         ,     quantity
         ,     TO_CHAR (Posting_Date, 'IYYYIW')     AS year_week
         ,      REGEXP_REPLACE ( material
                          , 'R..$'
                          )               AS base_material
         FROM    tblcon
    
         Where     tblcon.plant = '1900' 
         AND       tblcon.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
         AND       tblcon.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
         AND       tblcon.material IN('Label1R2C','Label2R1B')  '' *** It shall take all revisions and releases the material contained in the current period ***
    If you want to completely ignore certain rows, and not have them enter into any of the calculations, then conditions in got_base_materials, like you tried, are right. Is this giving you the results you want or not?
    )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (material)) 
                            OVER ( PARTITION BY  base_material
                                 ORDER BY      year_week  DESC
                           )          AS last_material
         ,          SUM (quantity)     AS week_quantity
         ,          year_week
         ,          plant
         ,       AVG (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_avg
         ,       STDDEV (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_stddev
         FROM          got_base_material
         GROUP BY  base_material
         ,            year_week
         ,          plant
    )
    SELECT       last_material
    ,       week_quantity
    ,       year_week
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( week_quantity
               - week_quantity_avg
               )          <= 2.0 * week_quantity_stddev '' *** Is this right way to adjust the outlier? ***
    Yes, that's what determines what an outlier is. Nothing else needs to be changed.
    Higher numbers mean fewer outliers. If the data is normally distributed, then
    roughly 68% of the points will be within 1.0 standard deviations of the average,
    roughly 96% of the points will be within 2.0 standard deviations of the average, and
    roughly 99.8% of the points will be within 3.0 standard deviations of the average.
    ORDER BY  last_material
    ,            year_week
    ,          plant
    ;
  • 12. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi,

    With the criteria specified, so I expect the same output as I posted earlier.
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier**
    Label1R2C     1900     1 165          1224
    Label1R2C     1900     11 634          1226
    Label2R1B     1900     1 165          1224
    Label2R1B     1900     11 634          1226
  • 13. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    I hope this will be right....
    WITH      got_base_material     AS
    (
         SELECT  material
         ,     plant
         ,     quantity
         ,     TO_CHAR (Posting_Date, 'IYYYIW')     AS year_week
         ,      REGEXP_REPLACE ( material
                                 , 'R..$'
                                 )               AS base_material
         FROM    tblcon
     
                 Where     tblcon.plant = '1900' 
                 AND       tblcon.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
                 AND       tblcon.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
     )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (material)) 
                            OVER ( PARTITION BY  base_material
                                      ORDER BY      year_week  DESC
                               )          AS last_material
         ,          SUM (quantity)     AS week_quantity
         ,          year_week
         ,          plant
         ,       AVG (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_avg
         ,       STDDEV (SUM (quantity))
                    OVER ( PARTITION BY  base_material
                              ,                    plant
                      )           AS week_quantity_stddev
         FROM          got_base_material
         GROUP BY  base_material
         ,            year_week
         ,          plant
    )
    SELECT       last_material
    ,       week_quantity
    ,       year_week
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( week_quantity
               - week_quantity_avg
               )          <= 2.0 * week_quantity_stddev 
    And    Last_material IN('Label1R2C','Label2R1B')
     
     
    ORDER BY  last_material
    ,            year_week
    ,          plant
    ;
  • 14. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi Frank,

    The weeks that has no value, I want to be presented as 0.
    I wish the results like this:
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier**
    Label1R2C     1900     0          1223   '**Outlier** ?
    Label1R2C     1900     1 165          1224
    Label1R2C     1900     0          1225   '**Outlier** ?
    Label1R2C     1900     11 634          1226
    Label2R1B     1900     0          1219     
    Label2R1B     1900     0          1220
    Label2R1B     1900     0          1221
    Label2R1B     1900     0          1222
    Label2R1B     1900     0          1223
    Label2R1B     1900     1 165          1224
    Label2R1B     1900     0          1225
    Label2R1B     1900     11 634          1226     '**Outlier** ?
    How do I complement your SQL with that?
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points