select
	case MyTable.MM
	when '01' then 'Jan'
	when '02' then 'Feb'
	... and so on..
	when '12' then 'Dec'
	end

SELECT..
FROM TableA A
JOIN TableB B ON A.somecolumn = b.somecolumn
JOIN TableC C on B.Somecol2 = C.Somecolumn3
WHERE A.somethin = 'xx'
AND B.Somethingelse = 'vv'

SELECT..
FROM TableA A, TableB B TableC C 
Where A.somecolumn = b.somecolumn
And B.Somecol2 = C.Somecolumn3
And A.somethin = 'xx'
AND B.Somethingelse = 'vv'

SELECT
	O.fn as [FTS],
	O.FullName,
	c.WRK_ID as [WRK ID],
	om1.SHORT_DESC_E as [WRK Type],
	c.WRK_ENTRY_DATE as [Entry Date],
	O.MANAGE_LOCATION as [Present Location],
	CASE c.WRK_SOURCE_INFO_DATE
	when '01' then 'Jan'
	when '02' then 'Feb'
	when '03' then 'Mar'
	when '04' then 'Apr'
	when '05' then 'May'
	when '06' then 'Jun'
	when '07' then 'Jul'
	when '08' then 'Aug'
	when '09' then 'Sep'
	when '10' then 'Oct'
	when '11' then 'Nov'
	when '12' then 'Dec'
	else 'No Entry'
	end as [Event Month], 
	ctc.SURNAME,
	ctc.FIRSTNAME,
	d.Date_Of_Creation
FROM
	rts_local.dbo.mainwork c,
	rts_local.dbo.ctc_personnel_table rts,
	profile.dbo.date d,
	profile.dbo.CLIENT O,
	rts_local.dbo.oml_table om,
	rts_local.dbo.oml_table om1
WHERE
	c.OID = O.oid
	AND c.WRK_RECORD_FINAL_FLAG = om.INTERNAL_CODE
	AND c.WRK_RECORDED_BY = rts.CTC_NUMBER
	AND c.WRK_SOURCE_INFO_CODE = om1.INTERNAL_CODE
	AND ((c.COMPLETING_OFFICE = '89900') AND
	(om.TABLE_TYPE_CODE = '0499') AND
	(om1.TABLE_TYPE_CODE = '0639') AND
	(c.WRK_SOURCE_INFO_DATE Between '2007-04-01' And
	'2008-03-31'))
ORDER BY
	c.WRK_RECORD_FINAL_DATE DESC

quote:

---

Originally posted by dinakar

You have an older way of joining tables and perhaps missing a few joins too. Use the ANSI standard.

SELECT..
FROM TableA A
JOIN TableB B ON A.somecolumn = b.somecolumn
JOIN TableC C on B.Somecol2 = C.Somecolumn3
WHERE A.somethin = 'xx'
AND B.Somethingelse = 'vv'

Instead of

SELECT..
FROM TableA A, TableB B TableC C 
Where A.somecolumn = b.somecolumn
And B.Somecol2 = C.Somecolumn3
And A.somethin = 'xx'
AND B.Somethingelse = 'vv'

Aside from this, is the WRK_SOURCE_INFO_DATE numeric or string type?


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

---

quote:

---

Originally posted by dinakar

You have an older way of joining tables and perhaps missing a few joins too. Use the ANSI standard.

SELECT..
FROM TableA A
JOIN TableB B ON A.somecolumn = b.somecolumn
JOIN TableC C on B.Somecol2 = C.Somecolumn3
WHERE A.somethin = 'xx'
AND B.Somethingelse = 'vv'

Instead of

SELECT..
FROM TableA A, TableB B TableC C 
Where A.somecolumn = b.somecolumn
And B.Somecol2 = C.Somecolumn3
And A.somethin = 'xx'
AND B.Somethingelse = 'vv'

Aside from this, is the WRK_SOURCE_INFO_DATE numeric or string type? This string is numberic with a date format of yyyy-mm-dd (i.e. 2007-06-22).


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

---

CASE c.WRK_SOURCE_INFO_DATE
	when '01' then 'Jan'
	when '02' then 'Feb'
	when '03' then 'Mar'
	when '04' then 'Apr'
	when '05' then 'May'
	when '06' then 'Jun'
	when '07' then 'Jul'
	when '08' then 'Aug'
	when '09' then 'Sep'
	when '10' then 'Oct'
	when '11' then 'Nov'
	when '12' then 'Dec'
	else 'No Entry'
	end as [Event Month], 

CASE MONTH(c.WRK_SOURCE_INFO_DATE)
	when 1 then 'Jan'
	when 2 then 'Feb'
	when 3 then 'Mar'
	when 4 then 'Apr'
	when 5 then 'May'
	when 6 then 'Jun'
	when 7 then 'Jul'
	when 8 then 'Aug'
	when 9 then 'Sep'
	when 10 then 'Oct'
	when 11 then 'Nov'
	when 12 then 'Dec'
	else 'No Entry'
	end as [Event Month], 

DECLARE	@Test TABLE (dt DATETIME)

INSERT	@Test
SELECT	NULL UNION ALL
SELECT	'20070101' UNION ALL
SELECT	'20070201' UNION ALL
SELECT	'20070301' UNION ALL
SELECT	'20070401' UNION ALL
SELECT	'20070501' UNION ALL
SELECT	'20070601' UNION ALL
SELECT	'20070701' UNION ALL
SELECT	'20070801' UNION ALL
SELECT	'20070901' UNION ALL
SELECT	'20071001' UNION ALL
SELECT	'20071101' UNION ALL
SELECT	'20071201'

SELECT	dt,
	CASE
		WHEN dt IS NULL THEN 'No Entry'
		ELSE LEFT(DATENAME(MONTH, dt), 3)
	END AS [Event Month],
	CASE
		WHEN dt IS NULL THEN 'No Entry'
		ELSE 'Q' + DATENAME(QUARTER, dt)
	END AS [Event Quarter]
FROM	@Test