CODEWITHSUNDEEP
sqloracle-database
satya Kumari
satya Kumari

Reputation: 7

Need to get previous financial year data in oracle sql

How can i write SQL which will fetch the data for last year. e.g. if i run this sql today (07-02-2020) so it should give me sum of lcy_amt data from 01-01-2019 to 31-12-2019;

trn_date      lcy_amount

08-01-2020    200
08-01-2019    100
02-11-2020    150

Please assist.

Upvotes: 0

Views: 769

Answers (2)

Nick
Nick

Reputation: 147146

You can compare the YEAR part of trn_date with one less than the YEAR of the current date e.g.

SELECT SUM(lcy_amount)
FROM yourtable
WHERE EXTRACT(year FROM trn_date) = EXTRACT(year FROM SYSDATE) - 1

To allow for use of indexes, it might be more efficient to use this query. Note that it relies on the values in trn_date having no time component; if they do then the query in @Tejash answer is more appropriate.

SELECT SUM(lcy_amount)
FROM yourtable
WHERE trn_date BETWEEN TRUNC(SYSDATE, 'YEAR') - INTERVAL '1' YEAR AND
       TRUNC(SYSDATE, 'YEAR') - INTERVAL '1' DAY

Demo on SQLFiddle

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use following query:

SELECT SUM(lcy_amount)
FROM yourtable
WHERE trn_date >= TRUNC(SYSDATE, 'YEAR') - INTERVAL '1' YEAR 
  AND trn_date < TRUNC(SYSDATE, 'YEAR')

The second query in the @nick's answer will exclude the 31-dec-2019 as it should be WHERE trunc(trn_date) ... else trn_date = 31-dec-2019 00:00:01 to 31-dec-2019 23:59:59 will be excluded from the result.

Cheers!!

Upvotes: 2

Related Questions