Help with date parameter and if/else statement

[George]

Hi,

I'm trying to run a select statement that takes includes an if/else
clause. I need to select the 'tran_date' between....

if the current month is greater than 10 i.e. after OCT then the
tran_date will be between '01-Oct' - plus current year or the
tran_date is '01-Oct' plus previous year.

and the current date

Here is my script so far:

SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,
b.description PROJECT_CODE, sysdate
FROM actrans a, acactivity b, dual c
WHERE a.resource_type = 'E'
AND a.acct_category = 'TIME'
and a.activity like '9-WW-357852%'
and b.activity = a.activity
and tran_date between

if to_char(sysdate, 'MM') > 10)
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY')
end
else
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))
End
ORDER BY tran_date

Can anyone help me out with this? I need to run it as a job, but not
sure whether I should be using a stored procedure etc..

Thanks in advance.
George

[Simon Hayes]

"George" <ge***********@eu.watsonwyatt.com> wrote in message
news:d4**************************@posting.google.c om...

Hi,

I'm trying to run a select statement that takes includes an if/else
clause. I need to select the 'tran_date' between....

if the current month is greater than 10 i.e. after OCT then the
tran_date will be between '01-Oct' - plus current year or the
tran_date is '01-Oct' plus previous year.

and the current date

Here is my script so far:

SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,
b.description PROJECT_CODE, sysdate
FROM actrans a, acactivity b, dual c
WHERE a.resource_type = 'E'
AND a.acct_category = 'TIME'
and a.activity like '9-WW-357852%'
and b.activity = a.activity
and tran_date between

if to_char(sysdate, 'MM') > 10)
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY')
end
else
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))
End
ORDER BY tran_date

Can anyone help me out with this? I need to run it as a job, but not
sure whether I should be using a stored procedure etc..

Thanks in advance.
George

Your code appears to be Oracle SQL, not Microsoft SQL, so I'm not sure how
you would write the query, given that date functions are quite different
between the two (as far as I know). And you also say "between", but you only
mention one date value, not two - perhaps you mean "greater than"? In any
event, what you're looking for is the CASE statement - in TSQL, one way to
write the query might be something like this:

SELECT
a.resource_code ASSOCIATE,
a.tran_date START_DATE,
b.description PROJECT_CODE,
current_timestamp
FROM
actrans a
inner join acactivity b
on b.activity = a.activity
WHERE
a.resource_type = 'E'
AND a.acct_category = 'TIME'
and a.activity like '9-WW-357852%'
and tran_date >
case
when datepart(mm, current_timestamp) > 10
then cast(convert(char(4), current_timestamp, 112)+'1001' as datetime)
else cast(cast(datepart(yy, current_timestamp)-1 as char(4)) + '1001' as
datetime)
end

Assuming you are using Oracle, you should post this in an Oracle newsgroup
for a better answer.

Simon