Forum Stats

  • 3,827,455 Users
  • 2,260,778 Discussions
  • 7,897,250 Comments

Discussions

Get Difference Between Timestamps Using Pivot

2»

Answers

  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 4, 2019 5:00PM

    Here is the sample you asked for.  I will have to post the output tomorrow.  The results would follow the same concept as my original table above in the first post, but the numbers are going to be different.

    insert into z_itemtable values ('1', 'PENCIL', '2019-08-26 05.00.00.000000000 PM', 'A');

    insert into z_itemtable values ('2', 'PENCIL', '2019-08-27 05.00.00.000000000 PM', 'B');

    insert into z_itemtable values ('3', 'PENCIL', '2019-08-28 05.00.00.000000000 PM', 'C');

    insert into z_itemtable values ('4', 'PENCIL', '2019-08-29 05.00.00.000000000 PM', 'D');

    insert into z_itemtable values ('5', 'PENCIL', '2019-08-30 05.00.00.000000000 PM', 'E');

    insert into z_itemtable values ('6', 'PEN', '2019-08-28 05.00.00.000000000 PM', 'F');

    insert into z_itemtable values ('7', 'PEN', '2019-08-29 05.00.00.000000000 PM', 'G');

    insert into z_itemtable values ('8', 'PEN', '2019-08-30 05.00.00.000000000 PM', 'H');

    insert into z_itemtable values ('9', 'ERASER', '2019-08-29 05.00.00.000000000 PM', 'I');

    insert into z_itemtable values ('10', 'ERASER', '2019-08-30 05.00.00.000000000 PM', 'J');

    insert into z_itemtable values ('11', 'PENCIL', '2019-08-27 05.00.00.000000000 PM', 'K');

    insert into z_itemtable values ('12', 'ERASER', '2019-08-29 05.00.00.000000000 PM', 'L');

    select * from z_itemtable;

    insert into z_mastertable values ('1', 'DT');

    insert into z_mastertable values ('2', 'DT');

    insert into z_mastertable values ('3', 'SB');

    insert into z_mastertable values ('4', 'SB');

    insert into z_mastertable values ('5', 'SB');

    insert into z_mastertable values ('6', 'EA');

    insert into z_mastertable values ('7', 'EA');

    insert into z_mastertable values ('8', 'EA');

    insert into z_mastertable values ('9', 'WS');

    insert into z_mastertable values ('10', 'WS');

    insert into z_mastertable values ('11', 'DT');

    insert into z_mastertable values ('12', 'DT');

    select * from z_mastertable;

    insert into z_storetable values ('DT', 'DOWNTOWN');

    insert into z_storetable values ('SB', 'SUBURB');

    insert into z_storetable values ('EA', 'EAST');

    insert into z_storetable values ('WS', 'WEST');

    select * from z_storetable;

    insert into z_itemtabledetail values ('A', '2019-09-30 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('B', '2019-09-15 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('C', '2019-09-12 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('D', '2019-09-26 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('E', '2019-09-22 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('F', '2019-09-03 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('G', '2019-09-01 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('H', '2019-09-11 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('I', '2019-09-22 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('J', '2019-09-14 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('K', '2019-09-02 05.00.00.000000000 PM');

    insert into z_itemtabledetail values ('L', '2019-09-07 05.00.00.000000000 PM');

    select * from z_itemtabledetail;

  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 4, 2019 5:03PM

    CREATE TABLE "Z_ITEMTABLE"

       ( "MASTERID" VARCHAR2(20 BYTE),

    "ITEMNAME" VARCHAR2(20 BYTE),

    "ITEMARRIVALDATE" TIMESTAMP (6),

    "DETAILID" VARCHAR2(20 BYTE)

       )

    CREATE TABLE "Z_ITEMTABLEDETAIL"

       ( "ID" VARCHAR2(20 BYTE),

    "DATESOLD" TIMESTAMP (6)

       )

    CREATE TABLE "Z_MASTERTABLE"

       ( "ID" VARCHAR2(20 BYTE),

    "STORECODE" VARCHAR2(20 BYTE)

       )

    CREATE TABLE "Z_STORETABLE"

       ( "CODE" VARCHAR2(20 BYTE),

    "STORENAME" VARCHAR2(20 BYTE)

       )

  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 4, 2019 5:05PM

    Partial...not working query

    select * from (

        select st.storename, it.itemname, trunc(it.itemarrivaldate) as datesoldz

        from z_itemtable it

        join z_mastertable m on it.masterid = m.id

        join z_storetable st on m.storecode = st.code

        join z_itemtabledetail itd on it.detailid = itd.id

        where it.itemarrivaldate >= to_date('2019-08-26', 'yyyy-mm-dd') and it.itemarrivaldate < to_date('2019-08-31', 'yyyy-mm-dd')

        )

        pivot

        (

        count(*)

        for datesoldz in (DATE '2019-08-26',

                          DATE '2019-08-27',

                          DATE '2019-08-28',

                          DATE '2019-08-29',

                          DATE '2019-08-30')

        )

    where storename in ('DOWNTOWN', 'SUBURB', 'EAST', 'WEST')

    group by storename, itemname, 3

    order by storename;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,071 Red Diamond
    edited Sep 4, 2019 8:24PM

    Hi,

    976563 wrote:Here is the sample you asked for. I will have to post the output tomorrow. The results would follow the same concept as my original table above in the first post, but the numbers are going to be different.insert into z_itemtable values ('1', 'PENCIL', '2019-08-26 05.00.00.000000000 PM', 'A');insert into z_itemtable values ('2', 'PENCIL', '2019-08-27 05.00.00.000000000 PM', 'B');...

    Many of those INSERT statements fail on my system

    Don't try to insert strings, such as '2019-08-26 05.00.00.000000000 PM', into TIMESTAMP columns, such as z_itemtable.itemarrivaldate.

  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 5, 2019 1:09PM

    Ahh, the INSERTs worked when I ran them.  I am on Oracle Database 11g Release 11.2.0.4.0 - 64bit.

    The desired output would be like...

    STOREITEMS2019-08-26 Sum
    2019-08-26 Avg2019-08-27 Sum2019-08-27 Avg2019-08-28 Sum2019-08-28 Avg2019-08-29 Sum2019-08-29 Avg2019-08-30 Sum2019-08-30 Avg
    DOWNTOWNERASER0000009900
    DOWNTOWNPENCIL35352512.5000000
    EASTPEN000066331212
    SUBURBPENCIL0000151528282323

    Row 1 ==> Only 1 found, the value is 9 because the (datesold minus itemarrivaldate [2019-09-07 minus 2019-08-29 ==> 9]).  This becomes the SUM and AVG.

    Row 2 ==> 2019-08-26 only 1 account is found. The value is 35 because (datesold minus itemarrivaldate [2019-09-30 minus 2019-08-26 ==> 35]).  This becomes the SUM and AVG.

    Row 2 ==> 2019-08-27 there are 2 accounts.  1st account (2019-09-15 minus 2019-08-27 ==> 19).  2nd account (2019-09-02 minus 2019-08-27 ==> 6).  19 + 6 => 25 SUM, 12.5 AVG

    Row 3 and 4 follow the same logic as Row 1 because there was only 1 row found.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,071 Red Diamond
    edited Sep 5, 2019 2:49PM Answer ✓

    Hi,

    976563 wrote:Ahh, the INSERTs worked when I ran them. I am on Oracle Database 11g Release 11.2.0.4.0 - 64bit.The desired output would be like...STOREITEMS2019-08-26 Sum
    2019-08-26 Avg2019-08-27 Sum2019-08-27 Avg2019-08-28 Sum2019-08-28 Avg2019-08-29 Sum2019-08-29 Avg2019-08-30 Sum2019-08-30 AvgDOWNTOWNERASER0000009900DOWNTOWNPENCIL35352512.5000000EASTPEN000066331212SUBURBPENCIL0000151528282323Row 1 ==> Only 1 found, the value is 9 because the (datesold minus itemarrivaldate [2019-09-07 minus 2019-08-29 ==> 9]). This becomes the SUM and AVG.Row 2 ==> 2019-08-26 only 1 account is found. The value is 35 because (datesold minus itemarrivaldate [2019-09-30 minus 2019-08-26 ==> 35]). This becomes the SUM and AVG.Row 2 ==> 2019-08-27 there are 2 accounts. 1st account (2019-09-15 minus 2019-08-27 ==> 19). 2nd account (2019-09-02 minus 2019-08-27 ==> 6). 19 + 6 => 25 SUM, 12.5 AVGRow 3 and 4 follow the same logic as Row 1 because there was only 1 row found.

    Earlier, you were interested in a subtotal showing all items from each store, as well as the individual store/item combinations.  Now you've changed the requirements to show only the individual items; is that right?

    Here's one way to do that:

    WITH    data_to_pivot    AS(    SELECT    st.storename    ,       it.itemname    ,         TRUNC (it.itemarrivaldate)  AS datearrived    ,         TRUNC (itd.datesold)            - TRUNC (it.itemarrivaldate)  AS ndays    FROM      z_itemtable         it    JOIN      z_mastertable        m  ON  it.masterid  = m.id    JOIN      z_storetable        st  ON  m.storecode  = st.code    JOIN      z_itemtabledetail  itd  ON  it.detailid  = itd.id    WHERE     it.itemarrivaldate  >= TO_DATE ('2019-08-26', 'yyyy-mm-dd')    AND       it.itemarrivaldate  <  TO_DATE ('2019-08-31', 'yyyy-mm-dd')    AND       st.storename        IN ('DOWNTOWN', 'SUBURB', 'EAST', 'WEST'))SELECT    *FROM      data_to_pivotPIVOT     (    SUM (ndays)   AS sum          ,    AVG (ndays)   AS avg          FOR  datearrived IN ( DATE '2019-08-26'  AS d_2019_08_26                              , DATE '2019-08-27'  AS d_2019_08_27                              , DATE '2019-08-28'  AS d_2019_08_28                              , DATE '2019-08-29'  AS d_2019_08_29                              , DATE '2019-08-30'  AS d_2019_08_30                              )   )ORDER BY  storename, itemname;

    The results I get are a little different from what you posted:

                                   D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019                               _08_26 _08_26 _08_27 _08_27 _08_28 _08_28 _08_29 _08_29 _08_30 _08_30STORENAME ITEMNAME               _SUM   _AVG   _SUM   _AVG   _SUM   _AVG   _SUM   _AVG   _SUM   _AVG--------- -------------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------DOWNTOWN  ERASER                                                              9      9DOWNTOWN  PENCIL                   35     35     25   12.5EAST      PEN                                                   6      6      3      3     12     12SUBURB    PENCIL                                               15     15     28     28     23     23WEST      ERASER                                                             24     24     15     15

    I get a row for storename='WEST'.  Did you mean to include that row in your desired results?

    As posted, the solution shows NULLs for missing data.  You can use NVL to get 0's instead, if you want them.

    User_OMEF8User_OMEF8
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 5, 2019 3:00PM
    Frank Kulash wrote:Earlier, you were interested in a subtotal showing all items from each store. Now you've changed the requirements to show only the individual items; is that right?

    Thank you for your help.  Yes, the requirements changed this morning after I spoke with the user (it seems to be very common from my experience unfortunately).  I greatly appreciate your help.  When I re-wrote the query, I used a WITH, but then I had all of my dates as a single column that went down by each row and not across as column headers.  The PIVOT you used differed much from mine, but I will decode your code to understand it better.  Thanks again!

  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Sep 5, 2019 3:16PM
    Frank Kulash wrote:I get a row for storename='WEST'. Did you mean to include that row in your desired results?As posted, the solution shows NULLs for missing data. You can use NVL to get 0's instead, if you want them.

    Sorry, I realized I did not answer your other question.  Yes, I had that in the result, but it looks like I did not include it in my desired result set.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,071 Red Diamond
    edited Sep 5, 2019 3:19PM

    Hi,

    976563 wrote:...When I re-wrote the query, I used a WITH, but then I had all of my dates as a single column that went down by each row and not across as column headers. The PIVOT you used differed much from mine, but I will decode your code to understand it better. Thanks again!

    To understand pivots, it often helps to run the exact same query without the pivot, like this:

    WITH    data_to_pivot    AS(    SELECT    st.storename    ,         it.itemname    ,         TRUNC (it.itemarrivaldate)  AS datearrived    ,         TRUNC (itd.datesold)            - TRUNC (it.itemarrivaldate)  AS ndays    FROM      z_itemtable         it    JOIN      z_mastertable        m  ON  it.masterid  = m.id    JOIN      z_storetable        st  ON  m.storecode  = st.code    JOIN      z_itemtabledetail  itd  ON  it.detailid  = itd.id    WHERE     it.itemarrivaldate  >= TO_DATE ('2019-08-26', 'yyyy-mm-dd')    AND       it.itemarrivaldate  <  TO_DATE ('2019-08-31', 'yyyy-mm-dd')    AND       st.storename        IN ('DOWNTOWN', 'SUBURB', 'EAST', 'WEST'))SELECT    *FROM      data_to_pivot/* PIVOT     (    SUM (ndays)   AS sum          ,    AVG (ndays)   AS avg          FOR  datearrived IN ( DATE '2019-08-26'  AS d_2019_08_26                              , DATE '2019-08-27'  AS d_2019_08_27                              , DATE '2019-08-28'  AS d_2019_08_28                              , DATE '2019-08-29'  AS d_2019_08_29                              , DATE '2019-08-30'  AS d_2019_08_30                              )   ) */ORDER BY  storename, itemname;

    Make sure you understand why this gets the results it does before trying to understand the pivot.