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