This discussion is archived
1 Reply Latest reply: Jun 20, 2013 5:31 AM by BluShadow RSS

Query

983563 Newbie
Currently Being Moderated

For each merch_id i want 24 hours count if for particular hour count is not

there it should return zero other wise it should return the count

for that particuler merch_id

 

 

WITH tranlog_t AS

(

SELECT   TO_CHAR (rec_time,'HH24') AS "rec_time",

           SUM(term_amt) AS "transaction_amt",

           COUNT (1) AS "transaction_count", mrch_id AS "MERCH_ID"

      FROM test

     WHERE rec_time BETWEEN TO_TIMESTAMP

                                                 ('06/01/2013 00:28:44',

                                                  'DD/MM/YYYY  HH24:MI:SS'

                                                 )

                                 AND TO_TIMESTAMP

                                                 ('06/06/2013 23:28:44',

                                                  'DD/MM/YYYY  HH24:MI:SS'

                                                 )

  GROUP BY TO_CHAR(rec_time,'HH24'),mrch_id

   ORDER BY rec_time,mrch_id),

all_y AS

     (SELECT  TO_CHAR (LEVEL - 1, 'FM00') hourpart

            FROM DUAL

      CONNECT BY LEVEL <= 24)

SELECT tranlog_t.*

  FROM tranlog_t LEFT OUTER JOIN all_y

       ON tranlog_t.rec_time = all_y.hourpart

Legend

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