Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get Difference Between Timestamps Using Pivot

User_OMEF8Aug 30 2019 — edited Sep 5 2019

Hello,

I am on Oracle 11g and I am trying to formulate a SQL query to produce results using a PIVOT (not sure if it is actually necessary).  The report that I want to create has different "Groups" of information and I was wondering if this could be achieved with a single query.  I know I can do this if I run multiple queries, then combine the information, then do some other operations to get what I need, but there are too many steps if I do it this way.

Here is what I want to do...  I have many stores and different items sold at each store.  For my purposes, I am only concerned with certain stores and certain items.  I also have a timestamp field that tells me when the item arrived and when it was sold.  I need to calculate the difference between the 2 timestamp fields (sold minus arrived, what is the difference between?)

  • Stores - varchar2
  • Items - varchar2
  • Arrived - Timestamp
  • Sold - Timestamp

Below is a sample output of what I want the report to look like.  Note that the column headers (2019-08-01, 2019-08-02, and 2019-08-03) would be dynamic (so I could have more dates across), but effectively, this would be the date difference of when the ITEM was sold.  For example, DOWNTOWN, PENCIL, 2019-08-01 ==> 5.  This is the calculated total number of days for all PENCILS sold that date.

If I had 5 PENCILS and they all had an Arrival Date of 2019-07-31 and a Sold Date of 2019-08-01.  This would equal 5 days.

If I had 3 PENCILS...

  • PENCIL 1 Arrival Date 2019-07-31 and Sold Date 2019-08-01 ==> 1 Day
  • PENCIL 2 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
  • PENCIL 3 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
  • Total Days ==> 5 days

STOREITEMS
2019-08-01
2019-08-022019-08-03
DOWNTOWNPEN123
DOWNTOWNPENCIL567
DOWNTOWNERASER213
2.67 (AVERAGE DATE DIFFERENCE)3 (AVERAGE DATE DIFFERENCE)4.33 AVERAGE DATE DIFFERENCE
4.SUBURBPEN987
SUBURBPENCIL321
SUBURBERASER456
5.33 AVERAGE DATE DIFFERENCE5 AVERAGE DATE DIFFERENCE4.67 AVERAGE DATE DIFFERENCE

Here is my attempt SQL query...

select * from (

     select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn)

     from itemtable i

     join storetable s on i.id = s.id

     join storenametable sn on s.storeid = sn.storename

     join itemdatetable d on i.itemid = d.id

     where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER')

     and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')

     )

     pivot

     (

     count(*)

     for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03')   <============ error missing IN keyword

     )

where storenamecolumn in ('DOWNTOWN', 'SUBURB')

order by storenamecolumn;

I keep getting the error above and I believe it has to do with the trunc() method.  If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp".  Any suggestions on how I can achieve my desired results?  Thanks.

Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column?  I edited the table above.

This post has been answered by Frank Kulash on Sep 5 2019
Jump to Answer

Comments

Phil Richens-Oracle
Hi Tomas,

Thank you very much for your thoughtful suggestions, and my apologies for not replying to you more promptly.

There are features planned for our next release that may help to answer some of your requirements. For the others, I will ensure that that they are considered for a future release.

[1] A "synchronize" feature planned for the next release enables a test to be remapped to a stored operation in a different schema or database, or even with a different signature. However, it also sounds like the ability to bind dynamically at run time would be useful, so this will be considered for a future release.

[2] PL/SQL level debug sounds like a great idea, but is not planned for the next release. I will ensure it is considered for a future release.

[3] Again this is a great idea, which will be considered for a future release.

[4] I think you can use suites to support this functionality. Suites enable you to group tests together and the tests can be ordered within the suite. In the next release, it is also planned to support nested suites.

[5] The "synchronize" feature planned for the next release should resolve this problem for you.

Here are some other new Unit Test features planned for our next release that may be useful to you: support for multiple start-ups and teardowns within a test or suite; improved parameter data type coverage.

I hope this helps to answer some of your concerns.

Best regards,
Philip.
687216
Hi Phil,

thanks for your answers.

1) OK, "synchronize" sounds nice.

2) OK, I understand this would require a significant changes to the framework, as now it calls anonymous PL/SQL blocks (and AFAIK it's not possible to debug anonymous block)

3) Great ;-)

4) I don't think so. Maybe in some really simple cases, when just calling a sequence of procedures. But a test suite is just group of unit tests (each test testing exactly one procedure), so when you need a little bit more complex logic (not just a sequence of procedures), you can't do that with a test suite. But I can live with this - writing a separate procedure encapsulating that test logic is not a big deal. And I perceive test suites as 'a group of logically related tests' rather than a 'complex test'.

5) OK, "synchronize" sounds nice.
687216
BTW we've found another 'strange thing' - the tests behave differently when executed from GUI and from a command-line. My colleague already reported that here: 1119070
1 - 3

Post Details

Added on Aug 30 2019
19 comments
975 views