Get Difference Between Timestamps Using Pivot

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;

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;

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...

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 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!