CODEWITHSUNDEEP
sqloracle-database
user11410351
user11410351

Reputation:

Modify query to return data from this year

I have a query which counts policy by tarifa.

select substr(tarifa,1,2), count(*)
from   pol p, uvod u, doppov d
where (datum_dop >='1-jan-12')  AND (datum_dop<='31-dec-12')
and izdavanje>='01-jul-07'
and p.orgjed = u.sorgz (+)
and DATUM_PREKIDA is not null
and p.polica=d.polica and d.pov_dopl='P'
and d.status='F'
and cisti_ao(p.polica)!=0 
group by substr(tarifa,1,2)

Now I have to modify this query to return only for the current year.

I tried to use the `extract' function but it doesn't work as I expected. How can I limit the data returned to the current year?

UPDATE

This is what I try so far:

select substr(tarifa,1,2), count(*)
from   pol p, uvod u, doppov d
where  EXTRACT( YEAR FROM datum_dop) = EXTRACT(YEAR FROM sysdate) 
and izdavanje>='01-jul-07'
and p.orgjed = u.sorgz (+)
and DATUM_PREKIDA is not null
and p.polica=d.polica and d.pov_dopl='P'
and d.status='F'
and cisti_ao(p.polica)!=0 
group by substr(tarifa,1,2)

Upvotes: 2

Views: 69

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Although this will work:

where  EXTRACT( YEAR FROM datum_dop) = EXTRACT(YEAR FROM sysdate)

it will prevent an index on datum_pop being considered - though it might not be used anyway if you're accessing a significant proportion of your data. Even so, another approach is to use a range:

where  datum_dop >= TRUNC(sysdate, 'YYYY') 
and    datum_pop < ADD_MONTHS(TRUNC(sysdate, 'YYYY'), 12)

The TRUNC(sysdate, 'YYYY') gives you midnight on the first day of the current year; ADD_MONTHS(..., 12) gives you midnight on the first day of next year.

You might not even need the second condition - it depends if datum_pop can have dates in the future, i.e. beyond sysdate.

Not directly related but

and izdavanje>='01-jul-07'

might work for you in your current session but it relies on implicit conversion of the string value to a date, and another user with different NLS settings might see wrong results or an error. You could use explicit conversion:

and izdavanje>=TO_DATE('01-jul-07', 'DD-Mon-RR', 'NLS_DATE_LANGUAGE=ENGLISH')

or with a format that doesn't rely on the language::

and izdavanje>=TO_DATE('2007-07-01', 'YYYY-MM-DD')

or with a date literal (if 8i supports those):

and izdavanje>=DATE '2007-07-01'

Upvotes: 3

Related Questions