Q

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?


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close