1 Reply Latest reply: Jun 20, 2013 7:31 AM by BluShadow RSS

    Query

    983563

      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