This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Dec 30, 2012 4:01 AM by 978776 RSS

Add a table in this query?

573145 Newbie
Currently Being Moderated
This summarizes the daily values ​​into weekly values ​​from a table.
create table tblBill (
mtrl   varchar2(10),
sales_quantity number,
posting_date date
);
 
 
insert into tblBill values('Label1',3100,'2012-05-01');
insert into tblBill values('Label1',1984,'2012-05-02');
insert into tblBill values('Label1',5670,'2012-05-03');
insert into tblBill values('Label1',30,'2012-05-04');
insert into tblBill values('Label1',3888,'2012-05-05');
insert into tblBill values('Label1',1651,'2012-05-06');
insert into tblBill values('Label1',1881,'2012-05-07');
insert into tblBill values('Label1',1985,'2012-05-08');
insert into tblBill values('Label1',3240,'2012-05-09');
insert into tblBill values('Label1',980,'2012-05-10');
insert into tblBill values('Label1',13165,'2012-05-17');
insert into tblBill values('Label1',1265,'2012-05-19');
insert into tblBill values('Label1',1165,'2012-05-23');
insert into tblBill values('Label1',3125,'2012-05-24');
insert into tblBill values('Label1',2311,'2012-05-29');



create table tblCon (
mtrl   varchar2(10),
con_quantity number,
posting_date date
);
 
 
insert into tblCon values('Label1',100,'2012-05-07');
insert into tblCon values('Label1',184,'2012-05-09');
insert into tblCon values('Label1',570,'2012-05-10');
insert into tblCon values('Label1',770,'2012-05-11');
insert into tblCon values('Label1',888,'2012-05-16');
insert into tblCon values('Label1',651,'2012-05-17');
insert into tblCon values('Label1',1081,'2012-05-18');
insert into tblCon values('Label1',1085,'2012-05-19');
insert into tblCon values('Label1',3240,'2012-05-20');
insert into tblCon values('Label1',990,'2012-05-24');
insert into tblCon values('Label1',1165,'2012-05-26');
insert into tblCon values('Label1',105,'2012-05-27');
insert into tblCon values('Label1',1165,'2012-05-28');
insert into tblCon values('Label1',2125,'2012-05-29');
insert into tblCon values('Label1',5311,'2012-05-30');
commit;
           SELECT  Material, qty as Quantity_Sales, fill_year_week as YearWeek
            from (
            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-05-30','yyyy-mm-dd') - to_date('2012-05-01','yyyy-mm-dd') ) + 1)) 
                SELECT  a.material ,fill_year_week ,SUM(NVL(sales_quantity,0)) AS QTY 
                FROM 
                 (   SELECT  t.material ,t.sales_quantity, TO_CHAR(t.posting_date,'IYYYIW') AS posting_week 
                         FROM    tblBill t 
                         WHERE   t.material = 'Label1' 
                        AND     t.Posting_date >= to_date('2012-05-01', 'yyyy-mm-dd') 
                        AND     t.Posting_date <= to_date('2012-05-30','yyyy-mm-dd') ) A
               PARTITION BY (a.material) RIGHT OUTER JOIN weeks_vw  ON weeks_vw.fill_year_week = A.posting_week 
               GROUP BY     a.material ,fill_year_week 
               Order by fill_year_week desc)
Now I want to add a second table that looks similar. Today, the answer looks like this:
MATERIALS QUANTITY YEAR_WEEK
Label1       8 086         1219
I want to add one to the column from Table 2 (tblCon) that the answer looks like this:
MATERIAL     SALES_QUANTITY     CON_QUANTITY     YEAR_WEEK
Label1             8 086             1 624             1219
How do I proceed?
  • 1. Re: Add a table in this query?
    user639304 Explorer
    Currently Being Moderated
    Hi,

    You wrote : "I want to add one to the column from Table 2 (tblCon) that the answer looks like this:",
    and we see 1624. It means that you get 1623 somewhere. But how is this value 1623 obtained?
  • 2. Re: Add a table in this query?
    573145 Newbie
    Currently Being Moderated
    Sorry I mean this:

    I want to add a column from Table 2, so the answer looks like this...
  • 3. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user570142 wrote:
    This summarizes the daily values ​​into weekly values ​​from a table.
    create table tblBill (
    mtrl   varchar2(10),
    sales_quantity number,
    posting_date date
    );
    
    
    insert into tblBill values('Label1',3100,'2012-05-01'); ...
    Thanks for posting the CREATE TABLE and INSERT statements. Remember why you go to all that trouble: it's to let the poeple who wnat to help you re-create the problem and test their ideas. If you post statements that don't work, its not very helpful.
    None of your INSERT statements work on my system, because you're trying to insert VARCHAR2S (such as '2012-05-01') into a DATE column. You should insert DATEs into DATE columns. use TO_DATE or DATE literals.
    SELECT  Material, qty as Quantity_Sales, fill_year_week as YearWeek
    from (
    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-05-30','yyyy-mm-dd') - to_date('2012-05-01','yyyy-mm-dd') ) + 1)) 
    SELECT  a.material ,fill_year_week ,SUM(NVL(sales_quantity,0)) AS QTY 
    FROM 
    (   SELECT  t.material ,t.sales_quantity, TO_CHAR(t.posting_date,'IYYYIW') AS posting_week 
    FROM    tblBill t 
    WHERE   t.material = 'Label1' 
    AND     t.Posting_date >= to_date('2012-05-01', 'yyyy-mm-dd') 
    AND     t.Posting_date <= to_date('2012-05-30','yyyy-mm-dd') ) A
    PARTITION BY (a.material) RIGHT OUTER JOIN weeks_vw  ON weeks_vw.fill_year_week = A.posting_week 
    GROUP BY     a.material ,fill_year_week 
    Order by fill_year_week desc)
    I don't believe this is the code you're actually running. It references a column called material, but there is no such column in the tbill table.
    Now I want to add a second table that looks similar. Today, the answer looks like this:
    MATERIALS QUANTITY YEAR_WEEK
    Label1       8 086         1219
    Again, this indicates you haven't posted your real query. The query above, if it could run, would produce 4 or 5 rows of output, one for each week. Post your actual code, and your actual, complete results.
    I want to add one to the column from Table 2 (tblCon) that the answer looks like this:
    MATERIAL     SALES_QUANTITY     CON_QUANTITY     YEAR_WEEK
    Label1             8 086             1 624             1219
    How do I proceed?
    It looks like a job for JOIN. Compute the weekly sum for one table in a separate sub-query, before joining the other table, something like this:
    WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-05-30', 'yyyy-mm-dd') AS last_posting_date
         FROM     dual
    )
    ,     weeks_vw     AS
    (
         SELECT     TRUNC (first_posting_date, 'IW') 
                  + (7 * (LEVEL - 1))              AS a_monday
         ,     TRUNC (first_posting_date, 'IW') 
                  + (7 *  LEVEL)              AS next_monday
         FROM    params
         CONNECT BY     LEVEL <= 1 + ( ( TRUNC (last_posting_date,  'IW')
                                     - TRUNC (first_posting_date, 'IW')
                               )
                             / 7
                             )
    )
    ,     tblbill_agg     AS
    (
         SELECT       w.a_monday
         ,       b.material
         ,       SUM (sales_quantity)     AS total_sales_quantity
         FROM          weeks_vw  w
         JOIN       tblbill   b  ON  b.posting_date >= w.a_monday
                            AND b.posting_date <  w.next_monday
         WHERE     b.material   IN ('Label1')
         GROUP BY  w.a_monday
         ,            b.material
    )
    SELECT       tc.mtrl
    ,       NVL ( MIN (ba.total_sales_quantity)
               , 0
               )                         AS total_sales_quantity
    ,       NVL ( SUM (tc.con_quantity)
               , 0
               )                         AS total_con_quantity
    ,       TO_CHAR (wv.a_monday, 'IYYY-IW')     AS year_week
    
    FROM                 weeks_vw     wv
    LEFT OUTER JOIN  tblbill_agg    ba  ON  ba.a_monday     =  wv.a_monday
    LEFT OUTER JOIN  tblcon          tc  PARTITION BY  (tc.mtrl)
                                     ON  tc.posting_date >= wv.a_monday
                            AND     tc.posting_date <  wv.next_monday
    GROUP BY  tc.mtrl     
    ,            wv.a_monday
    ORDER BY  tc.mtrl     
    ,            wv.a_monday
    ;
  • 4. Re: Add a table in this query?
    573145 Newbie
    Currently Being Moderated
    Hi Frank,

    The query is the one I use. However, I took a copy of the CREATE TABLE and INSERT statements from another example, so that I missed to change from Mtrl to Material. Thanks for all information.

    Anyway, this is what I seek. A follow-up question ...
    One thing that I had before is a variable to easily summarize by month or week. The user then changed just 'IYYYIW' to 'yyyymm' and YearWeek to YearMonth. But how do I make such a variable in this query?
  • 5. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    ... One thing that I had before is a variable to easily summarize by month or week. The user then changed just 'IYYYIW' to 'yyyymm' and YearWeek to YearMonth. But how do I make such a variable in this query?
    You can add it to the SELECT clause in the first suib-query, PARAMS.

    You can use it in CASE expressions in the next sub-query. WEEKS_VW will no longer be a good name, you'll want something more generic, such as PERIODS, and you'll want the columns to be something like PERIOD_START and NEXT_PERIOD_START.
    You'll want to change YEAR_WEEK in the main query, too.

    If you have trouble, post your best attempt, and the other information mentioned in the forum FAQ {message:id=9360002}.
  • 6. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    Hi again,

    Unfortunately, I will not get anywhere. I changed to the more general period of naming. But then I got stuck completely.
    WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-05-30', 'yyyy-mm-dd') AS last_posting_date
         FROM     dual
    )
    ,     periods_vw     AS
    (
         SELECT     TRUNC (first_posting_date, 'IW') 
                  + (7 * (LEVEL - 1))              AS a_period_start
         ,     TRUNC (first_posting_date, 'IW') 
                  + (7 *  LEVEL)              AS next_period_start
         FROM    params
         CONNECT BY     LEVEL <= 1 + ( ( TRUNC (last_posting_date,  'IW')
                                     - TRUNC (first_posting_date, 'IW')
                               )
                             / 7
                             )
    )
    ,     tblbill_agg     AS
    (
         SELECT       w.a_period_start
         ,       b.material
         ,       SUM (sales_quantity)     AS total_sales_quantity
         FROM          periods_vw  w
         JOIN       tblbill   b  ON  b.posting_date >= w.a_period_start
                            AND b.posting_date <  w.next_period_start
         WHERE     b.material   IN ('Label1')
         GROUP BY  w.a_period_start
         ,            b.material
    )
    SELECT       tc.material
    ,       NVL ( MIN (ba.total_sales_quantity)
               , 0
               )                         AS total_sales_quantity
    ,       NVL ( SUM (tc.con_quantity)
               , 0
               )                         AS total_con_quantity
    ,       TO_CHAR (wv.a_period_start, 'IYYY-IW')     AS year_period
     
    FROM                 periods_vw     wv
    LEFT OUTER JOIN  tblbill_agg    ba  ON  ba.a_period_start     =  wv.a_period_start
    LEFT OUTER JOIN  tblcon          tc  PARTITION BY  (tc.material)
                                     ON  tc.posting_date >= wv.a_period_start
                            AND     tc.posting_date <  wv.next_period_start
    GROUP BY  tc.material     
    ,            wv.a_period_start
    ORDER BY  tc.material     
    ,            wv.a_period_start
    ;
    A question off the record ...

    I do not know what happened ... I changed my email (got married and changed my last name). After the change, it is like this in my account:

    user570142
    Posts: 1
    Registered: 12/08/12

    I want it to be changed back to the correct registration date and so can also see my previous posts.

    *570142*
    Posts: 218
    Registered: 04/18/07

    Who can help me with this? I can not find support for community?
  • 7. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user570142 wrote:
    Hi again,

    Unfortunately, I will not get anywhere.
    Not if you don't try.

    How hard is it to add a flag to the params sub-query, like I suggested?
    WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-05-30', 'yyyy-mm-dd') AS last_posting_date
         ,     'MONTH'                                AS week_or_month
         FROM     dual
    ) ...
    Here's an example of how you could use that flag in a CASE expression.
    The sub-query PERIODS_VW uses "CONNECT BY LEVEL <= n" to generate n rows. When we're grouping by weeks, we round the dates down to the beginning of their weeks, and then count the number of weeks between them. When we're grouping by months, what will chnge? We'll do the same thing, except that we'll round the dates down to the beginning of their months, and count the number of months between them. Therefore, the CONNECT BY clause will change to something like:
    ...     CONNECT BY     LEVEL <= 1 + CASE
                                 WHEN  week_or_month = 'WEEK'
                              THEN  ( ( TRUNC (last_posting_date,  'IW')
                                                       - TRUNC (first_posting_date, 'IW')
                                          )
                                         / 7
                                         )
                              ELSE  MONTHS_BETWEEN ( TRUNC (last_posting_date,  'MONTH')
                                                    , TRUNC (first_posting_date, 'MONTH')
                                                    )
                                   END
    A question off the record ...
    Acutally, it's on the record, but off the topic.
    Who can help me with this? I can not find support for community?
    Post a question on the Community Feedback and Suggestions (Do Not Post Product-Related Questions Here). This site does some strange things with usernames.
  • 8. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    Hi again,

    Thanks for your help and for your detailed reply. It's very valuable for me when I learn simultaneously.

    When I try to run on a monthly and change from 'IYYYIW' to 'YYYYMM', it does not show summation correctly. I also changes from WEEK to MONTH in PARAMS.
    I get a row, but this is the first line of the week.

    I also get mismatch when I try to select a different material. These two tables contain many materials. The answer is on all material instead of the one I selected.

    If I add Label2 in tblCon:
    insert into tblCon values('Label2',100,'2012-05-07');
    insert into tblCon values('Label2',184,'2012-05-09');
    insert into tblCon values('Label2',570,'2012-05-10');
    commit;
    I get the following results:
    Material     Total_Sales_Quantity     Total_Con_Quantity     Year_Period     
    -------             -------------------     -----------------     -----------
    Label1          7553               0               201218     
    Label1          8086               1624               201219     
    Label1          14430               6945               201220     
    Label1          4290               2260               201221     
    Label1          2311               8601               201222     
    Label2          7553               0               201218     
    Label2          8086               854               201219     
    Label2          14430               0               201220     
    Label2          4290               0               201221     
    Label2          2311               0               201222
    The result should have been this:
    Material     Total_Sales_Quantity     Total_Con_Quantity     Year_Period     
    -------             -------------------     -----------------     -----------
    Label1          7553               0               201218     
    Label1          8086               1624               201219     
    Label1          14430               6945               201220     
    Label1          4290               2260               201221     
    Label1          2311               8601               201222     
    Label2          0               0               201218     
    Label2          0               854               201219     
    Label2          0               0               201220     
    Label2          0               0               201221     
    Label2          0               0               201222
  • 9. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    ... When I try to run on a monthly and change from 'IYYYIW' to 'YYYYMM', it does not show summation correctly. I also changes from WEEK to MONTH in PARAMS.
    I get a row, but this is the first line of the week.
    It's very hard for me to say what you're doing wrong when I don't know what you're doing. Post your code, and the full, exact results you want from the sample data. Make sure the sample data includes data from different months; one row for one table in June might be enough.
    I also get mismatch when I try to select a different material. These two tables contain many materials. The answer is on all material instead of the one I selected.
    Sorry, my mistake. The main query needs to join on material as well as period, and you'd better do a FULL OUTER JOIN, because some period/material combinations may occur in one table, but not the other. You may find it easier to do a UNION rather than a FULL OUTER JOIN.
    If I add Label2 in tblCon:
    insert into tblCon values('Label2',100,'2012-05-07');
    insert into tblCon values('Label2',184,'2012-05-09');
    insert into tblCon values('Label2',570,'2012-05-10');
    commit;
    It looks like you haven't read my earlier message {message:id=19735622} You're still trying to ibsert VARCHAR2 values into a DATE column. Getting the totals the way you want them is much harder than using the TO_DATE or DATE literals. Maybe you should work on the easier parts before attempting the harder stuff.
    ... The result should have been this:
    Material     Total_Sales_Quantity     Total_Con_Quantity     Year_Period     
    -------             -------------------     -----------------     -----------
    Label1          7553               0               201218     ...
    Why do you want 7553, and not 16323?
  • 10. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    I'll try to solve it myself otherwise I will return with a complete example.

    First, I'll try to solve it with a join on materials.

    I have read your tidagre posts, but I totally missed that I should take into account the date.



    I myself had changed in my test data so I got the wrong value. The correct value should of course be 16323

    Edited by: 570142 on Dec 10, 2012 1:25 PM
  • 11. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    I have solved the problem of shifting from weeks to months. I myself had done wrong.

    Frank Kulash wrote:
    Sorry, my mistake. The main query needs to join on material as well as period, and you'd better do a FULL OUTER JOIN, because some period/material combinations may occur in one table, but not the other. You may find it easier to do a UNION rather than a FULL OUTER JOIN.
    But I can not solve it to join materials on the main query? What exactly do you mean?
  • 12. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way to do what you want using UNION rather than join:
    WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-06-30', 'yyyy-mm-dd') AS last_posting_date
         ,     'MONTH'                                AS week_or_month
         FROM     dual
    )
    ,     periods_vw     AS
    (
         SELECT     CASE
                  WHEN  week_or_month = 'WEEK'
                  THEN  TRUNC (first_posting_date, 'IW') 
                         + (7 * (LEVEL - 1))
                  ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                   , LEVEL - 1
                             )
              END                    AS a_period_start
         ,     CASE
                  WHEN  week_or_month = 'WEEK'
                  THEN  TRUNC (first_posting_date, 'IW') 
                         + (7 * LEVEL)
                  ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                   , LEVEL
                             )
              END                    AS next_period_start
         ,     CASE
                  WHEN  week_or_month = 'WEEK'
                  THEN  'IYYYIW'
                  ELSE  'YYYYMM'
              END                    AS date_format
         FROM    params
         CONNECT BY     LEVEL <= 1 + CASE
                                 WHEN  week_or_month = 'WEEK'
                              THEN  ( ( TRUNC (last_posting_date,  'IW')
                                                       - TRUNC (first_posting_date, 'IW')
                                          )
                                         / 7
                                         )
                              ELSE  MONTHS_BETWEEN ( TRUNC (last_posting_date,  'MONTH')
                                                    , TRUNC (first_posting_date, 'MONTH')
                                                    )
                                   END
    )
    ,     agg     AS
    (
         SELECT       b.material
         ,       b.sales_quantity
         ,       0          AS con_quantity
         ,       pb.a_period_start
         ,       pb.date_format
         FROM            periods_vw  pb
         LEFT OUTER JOIN       tblbill     b  PARTITION BY (b.material)
                                      ON  b.posting_date >= pb.a_period_start
                                       AND b.posting_date <  pb.next_period_start
         WHERE     b.material   IN ('Label1', 'Label2')
        UNION ALL
         SELECT       c.mtrl
         ,       0
         ,       c.con_quantity
         ,       pc.a_period_start
         ,       pc.date_format
         FROM            periods_vw  pc
         LEFT OUTER JOIN       tblcon      c  PARTITION BY (c.mtrl)
                                      ON  c.posting_date >= pc.a_period_start
                                       AND c.posting_date <  pc.next_period_start
         WHERE     c.mtrl     IN ('Label1', 'Label2')
    )
    SELECT       material
    ,       NVL (SUM (sales_quantity), 0)          AS total_sales_quantity
    ,       NVL (SUM (con_quantity),   0)          AS total_con_quantity
    ,       TO_CHAR ( a_period_start
                  , MIN (date_format)
                )               AS period
    FROM       agg
    GROUP BY  material
    ,            a_period_start
    ORDER BY  material
    ,            a_period_start
    ;
  • 13. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    Hi,

    This is exactly what I need. Thank you!
    Now I have only one problem. I'll try to use this in. Net. The problem is that I have to start with SQL Select instead of With in Net.
    How do I do that in the best way?

    Like this:
    Select material, total_Sales_quantity, total_con_quantity, period 
    from
    (WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-06-30', 'yyyy-mm-dd') AS last_posting_date
         ,     'MONTH'                                AS week_or_month
         FROM     dual
    ....
    ....
  • 14. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    Hi,

    This is exactly what I need. Thank you!
    Now I have only one problem. I'll try to use this in. Net. The problem is that I have to start with SQL Select instead of With in Net.
    How do I do that in the best way?

    Like this:
    Select material, total_Sales_quantity, total_con_quantity, period 
    from
    (WITH     params          AS
    (
         SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
         ,     TO_DATE ('2012-06-30', 'yyyy-mm-dd') AS last_posting_date
         ,     'MONTH'                                AS week_or_month
         FROM     dual
    ....
    ....
    Yes; or
    SELECT  *
    from
    (   WITH     params          AS
    ...
    or, depending on what you mean by "best":
    SELECT       material
    ,       NVL (SUM (sales_quantity), 0)          AS total_sales_quantity
    ,       NVL (SUM (con_quantity),   0)          AS total_con_quantity
    ,       TO_CHAR ( a_period_start
                  , MIN (date_format)
                )               AS period
    FROM       (
                  WITH  params  AS 
                  ...
              )
    GROUP BY  material
    ,            a_period_start
    ORDER BY  material
    ,            a_period_start
    ;
    I don't see much difference among them.
1 2 Previous Next

Legend

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