1 Reply Latest reply on Jun 20, 2013 12:31 PM by BluShadow



      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