Select query using case function

From: George <georgina.wren_at_eu.watsonwyatt.com>
Date: 2 Aug 2004 07:14:51 -0700
Message-ID: <d45230d0.0408020614.28531a28@posting.google.com>

Hi,

I'm having a few problems getting my head round this select query. I know the basics of sql, but this is just a bit too complex for me.

I need to select data from two tables and one of the conditions is to select the transaction date between two dates. I need to include a case function (or at least I think I do?), to determine what the first date will be:

So where the sysdate month is greater than 10 i.e. month of Oct then the transaction date is equal to '01-Oct-YY', YY - needs be current year.
else the transaction date is equal to '01-Oct-YY', YY - needs be previous year.

The second date is the current date i.e. sysdate

Here is what I have so far: Can anyone help with this?

SELECT a.resource_code ASSOCIATE, b.description PROJECT_CODE, a.tran_date START_DATE
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

a.tran_date between
Case when (trunc(sysdate, 'MM')) > 10

	then '01-Oct' || to_char(sysdate, 'YY')
	else '01-Oct' || to_char(sysdate, 'YY'- 1)
	and sysdate

ORDER BY tran_date

Thanks
George Received on Mon Aug 02 2004 - 09:14:51 CDT