Skip to Main Content
Forums

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!

Need to populate ZERO for the sales not present for the given period.

user2078915Jun 2 2016 — edited Jun 2 2016

I have the below query:

SELECT 

TO_CHAR((SYS_EXTRACT_UTC(CAST(TRUNC(co.created_dtm, 'MI') AS TIMESTAMP))),'MM/DD/YYYY HH24:MI:SS:FF') ||' UTC' utc_date,

COUNT(DISTINCT co.order_no) order_cnt

FROM  

CUSTOMER_ORDER co

WHERE  

co.created_dtm BETWEEN TRUNC(SYSDATE, 'MI') - (2/24) AND TRUNC(SYSDATE, 'MI')

GROUP BY TO_CHAR((SYS_EXTRACT_UTC(CAST(TRUNC(co.created_dtm, 'MI') AS TIMESTAMP))),'MM/DD/YYYY HH24:MI:SS:FF') ||' UTC'

The output as follows:

  

UTC_DATEORDER_CNT
06/02/2016 16:55:00:000000 UTC1
06/02/2016 16:58:00:000000 UTC1
06/02/2016 16:59:00:000000 UTC1
06/02/2016 17:02:00:000000 UTC4


Between 16:55:00 and 16:58:00, I don't have sales. I want the the sales value as ZERO for this period. For example :


  

UTC_DATEORDER_CNT
06/02/2016 16:55:00:000000 UTC1
06/02/2016 16:56:00:000000 UTC0
06/02/2016 16:57:00:000000 UTC0
06/02/2016 16:58:00:000000 UTC1
06/02/2016 16:59:00:000000 UTC1
06/02/2016 17:00:00:000000 UTC0
06/02/2016 17:01:00:000000 UTC0
06/02/2016 17:02:00:000000 UTC4

Could someone help us to share the logic to be used?
This post has been answered by Paulzip on Jun 2 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 30 2016
Added on Jun 2 2016
16 comments
361 views