A view for a MAX value in any month

I need to develop a view which retrieves a max meter reading from a current month and a max meter reading from a month previous for a certain unit. The view needs to retrieve the unit id, max meter for the current month, and max meter for the prior month.

I've tried the following code, but it works only for the current or a single month.

SELECT UNIT_ID, 
MIN(MIN_ODOM) AS BEGIN_ODOMETER,
MAX(MIN_ODOM) AS ENDING_ODOMETER,
TRUNC(MAX(TRANS_DATE)) AS DATE_FUELED
FROM 
( SELECT DISTINCT 
UNIT_USAGE.UNIT_ID,
UNIT_USAGE.USAGE AS MIN_ODOM,
UNIT_USAGE.TRANS_DATE 
FROM UNIT_USAGE )
GROUP BY UNIT_ID

Is this possible by a view?


    Requires Free Membership to View

    Login

Yes, you can use a view in this situation. Instead of a view to retrieve max data from both current and previous months, write a view to retrieve max data from a single month, and use it twice. Your description of the problem mentioned only a max value, but your query has both min and max, so you may want to write a similar min view if you need it.

create 
  view maxreading
     ( unit_id
     , max_odom
     , max_date
     )
as
select unit_id
     , usage 
     , trans_date 
  from unit_usage  ZZ
 where usage
     = ( select max(usage)
           from unit_usage 
          where unit_id 
              = ZZ.unit_id
            and year(trans_date) 
              = year(ZZ.trans_date)
            and month(trans_date) 
              = month(ZZ.trans_date)
       )

The correlated subquery in the view selects the highest usage for the month. So when this view is used, you need to supply a WHERE condition for which month. From your use of the TRUNCATE function, you're likely using Oracle, so the function for the current date is SYSDATE.

select unit_id
     , curr.max_odom  as currmonth_max_odom
     , curr.max_date  as currmonth_max_date 
     , prev.max_odom  as prevmonth_max_odom
     , prev.max_date  as prevmonth_max_date 
  from maxreading curr
inner
  join maxreading prev
    on curr.unit_id = prev.unit_id
 where year(curr.max_date) = year(SYSDATE)
   and month(curr.max_date) = month(SYSDATE)
   and year(prev.max_date) 
                = year((ADD_MONTHS(SYSDATE,-1))
   and month(prev.max_date) 
                = month((ADD_MONTHS(SYSDATE,-1))

This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top