| re: Help with date parameter and if/else statement
"George" <georgina.wren@eu.watsonwyatt.com> wrote in message
news:d45230d0.0407280304.3a727f09@posting.google.c om...[color=blue]
> 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[/color]
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
| 
