CODEWITHSUNDEEP
sqloracle-database
Peter
Peter

Reputation: 163

Get rows from current month if older is not available

I have a table that looks like this:

+--------------------+---------+
|    Month (date)    |  amount |
+--------------------+---------+
| 2016-10-01         | 20      |
| 2016-08-01         | 10      |
| 2016-07-01         | 17      |
+--------------------+---------+

I'm looking for a query (sql statement) which satisfies the following conditions:

In the example table the row I'm looking for would be this:

+--------------------+---------+
| 2016-08-01         | 10      |
+--------------------+---------+

Has anyone a idea for a non complex select query?

Thanks in advance, Peter

Upvotes: 3

Views: 77

Answers (6)

am2
am2

Reputation: 371

You could sort the data in the direction you want to:

with MyData as
(
  SELECT to_date('2016-10-01','YYYY-MM-DD') MY_DATE, 20 AMOUNT FROM DUAL UNION
  SELECT to_date('2016-08-01','YYYY-MM-DD') MY_DATE, 10 AMOUNT FROM DUAL UNION
  SELECT to_date('2016-07-01','YYYY-MM-DD') MY_DATE, 17 AMOUNT FROM DUAL 
),
MyResult AS (
  SELECT 
    D.*  
  FROM MyData D
  ORDER BY 
    DECODE(
      12*TO_CHAR(MY_DATE,'YYYY') + TO_CHAR(MY_DATE,'MM'),
      12*TO_CHAR(SYSDATE,'YYYY') + TO_CHAR(SYSDATE,'MM'),
      -1,
      12*TO_CHAR(MY_DATE,'YYYY') + TO_CHAR(MY_DATE,'MM')) 
  DESC
)
SELECT * FROM MyResult WHERE RowNum = 1

Upvotes: 0

user5683823
user5683823

Reputation:

I added more data for testing, and an "id" column (a more realistic scenario) to show how this would work. If there is no "id" in your data, simply delete any reference to it from the solution.

Notes - month is a reserved Oracle word, don't use it as a column name. The solution assumes the date column contains dates that are already truncated to the beginning of the month. The trick in "order by" in the dense_rank last is to assign a value (ANY value!) when the month is the current month; by default, the value assigned to all other months is NULL, which by default come after any non-null value in an ascending order.

You may want to test the various solutions for efficiency if execution time is important.

with
     inputs ( id, mth, amount ) as (
       select 1, date '2016-10-01', 20 from dual union all
       select 1, date '2016-08-01', 10 from dual union all
       select 1, date '2016-07-01', 17 from dual union all
       select 2, date '2016-10-01', 30 from dual union all
       select 2, date '2016-09-01', 25 from dual union all
       select 3, date '2016-10-01', 20 from dual union all
       select 4, date '2016-08-01', 45 from dual union all
       select 4, date '2016-06-01', 30 from dual
     )
-- end of TEST DATA - the solution (SQL query) is below this line
select id,
       max(mth) keep(dense_rank last order by
                case when mth = trunc(sysdate, 'mm') then 0 end, mth) as mth,
       max(amount) keep(dense_rank last order by
                case when mth = trunc(sysdate, 'mm') then 0 end, mth) as amount
from inputs
group by id
order by id   -- ORDER BY is optional
;


 ID MTH         AMOUNT
--- ---------- -------
  1 2016-08-01      10
  2 2016-09-01      25
  3 2016-10-01      20
  4 2016-08-01      45

Upvotes: 0

AlexSmet
AlexSmet

Reputation: 2161

Another way using MAX

WITH tbl AS (
    SELECT TO_DATE('2016-10-01', 'YYYY-MM-DD') AS "month", 20 AS amount FROM dual
    UNION 
    SELECT TO_DATE('2016-08-01', 'YYYY-MM-DD') AS "month", 10 AS amount FROM dual    
    UNION 
    SELECT TO_DATE('2016-07-01', 'YYYY-MM-DD') AS "month", 5 AS amount FROM dual        
)
SELECT * 
  FROM tbl
 WHERE TRUNC("month", 'MONTH') = NVL((SELECT MAX(t."month") 
                                        FROM  tbl t 
                                       WHERE t."month" < TRUNC(SYSDATE, 'MONTH')), 
                                     TRUNC(SYSDATE, 'MONTH'));

Upvotes: 1

Kacper
Kacper

Reputation: 4818

It's not the nicest query but it should work.

select amount, date from (
  select amount, date, row_number over(partition by HERE_PUT_ID order by 
    case trunc(date, 'month') when trunc(sysdate, 'month') then to_date('00010101', 'yyyymmdd') else trunc(date, 'month')  end
    desc) r)
where r = 1;

I guess you have some id in table so put id column instead of HERE_PUT_ID if you want query for whole table just delete: partition by HERE_PUT_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would use row_number():

select t.*
from (select t.*,
             row_number() over (order by (case when to_char(dte, 'YYYY-MM') = to_char(sysdate, 'YYYY-MM') then 1 else 2 end) desc,
                                          dte desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Actually, you don't need row_number() for this:

select t.*
from (select t.*
      from t
      order by (case when to_char(dte, 'YYYY-MM') = to_char(sysdate, 'YYYY-MM') then 1 else 2 end) desc,
               dte desc
     ) t
where rownum = 1;

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

You may need the following:

SELECT *
  FROM (  SELECT *
            FROM test
           WHERE TRUNC(SYSDATE, 'month') >= month
        ORDER BY CASE
                    WHEN TRUNC(SYSDATE, 'month') = month
                        THEN 0 /* if current month, ordered last */
                        ELSE 1 /* previous months are ordered first */
                    END DESC,
                    month DESC /* among previous months, the greatest first */
       )
 WHERE ROWNUM = 1

Upvotes: 3

Related Questions