1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Get value only for a given day in a month (or max values)

Discussion in 'SQL PL/SQL' started by Jan Bergan, Aug 31, 2016.

  1. Jan Bergan

    Jan Bergan Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Norway
    Hi,
    Does anyone know if it's possible to extract only values for the firs day each month, in the timespan given by the 'UTC_READ_TIME' in the code below?
    Alternatively get the highest value of the 'TempOut' for each month pr EAN id?

    Code (SQL):
    WITH pivot_data AS (
        SELECT b.Udc_id AS EAN, A.UTC_READ_TIME AS TIME, decode(c. physical_channel_no, '18', 'm3', '35', 'TempInn', '36', 'TempUt') AS kanal, a.cum_read
        FROM register_reads a
          LEFT OUTER JOIN lookup_mv_channels1 c
            ON c.Channel_id = A.channel_id
          LEFT OUTER JOIN lookup_mv_sdps b
            ON b.meter_id = c.meter_id
            WHERE A.UTC_READ_TIME >= to_date('1/1/2016 01:00:00', 'DD/MM/YYYY HH24:MI:SS')
            AND A.UTC_READ_TIME <= to_date('31/8/2016 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
            AND b.udc_id LIKE 'LIL-%'
            AND c. physical_channel_no IN ('18', '35', '36') -- [Wather/TempIn/TempOut]
            )
    SELECT *
    FROM pivot_data
    PIVOT (MAX(cum_read) FOR kanal IN ('m3', 'TempIn', 'TempOut'))
    ORDER BY EAN, TIME
    This results in a very large list (se attachment), as in prints 8760 values (hours in a year) for each EAN identity.

    Kind regards
    Jan
     

    Attached Files:

  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    for example : trunc sysdate on first day by month and day on month
    Code (SQL):
    SELECT sysdate,trunc(sysdate,'mm'),to_char(sysdate,'dd') FROM dual;

    use an additional predicate: 'and to_number (to_char (A.UTC_READ_TIME, 'dd')) = 1'
     
    Last edited: Aug 31, 2016
    Jan Bergan likes this.
  3. Jan Bergan

    Jan Bergan Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Norway

    Thanks Sergey,
    This solved my issue :)