Help with SQL statement

[DKenny]

Here's a 'One-pass' method
Note: I had to do this on SQLServer and I think you need a & rather than a + to concat string fields in Oracle.

SELECT	DEPT
	,SUM(AMOUNT)
FROM	DK_TEST
WHERE	CAST(CAST(MYYEAR AS CHAR(4))+ CAST([WEEK]  AS CHAR(2))AS INT) 
BETWEEN 200512 AND 200601
GROUP BY
	DEPT

[D-niss]

Here's a 'One-pass' method
Note: I had to do this on SQLServer and I think you need a & rather than a + to concat string fields in Oracle.

SELECT	DEPT
	,SUM(AMOUNT)
FROM	DK_TEST
WHERE	CAST(CAST(MYYEAR AS CHAR(4))+ CAST([WEEK]  AS CHAR(2))AS INT) 
BETWEEN 200512 AND 200601
GROUP BY
	DEPT

If I may call upon you help again, what does CAST stands for? Also would the CHAR(2) take care of the 0 in front of single digit week number?
Tx

[DKenny]

If I may call upon you help again, what does CAST stands for?

CAST is use to change the Type of a value.

Synthax:
CAST( Value as Type)

So CAST(MYYEAR AS CHAR(4)) converts the values in the field MYYEAR to a fixed length string, 4 characters long.

Also would the CHAR(2) take care of the 0 in front of single digit week number?
Tx

Yes, the CHAR datatype is fixed length, and casting an Integer as a CHAR will always add preceding zeros.

[D-niss]

CAST is use to change the Type of a value.

Synthax:
CAST( Value as Type)

So CAST(MYYEAR AS CHAR(4)) converts the values in the field MYYEAR to a fixed length string, 4 characters long.


Yes, the CHAR datatype is fixed length, and casting an Integer as a CHAR will always add preceding zeros.

ahhh this is beautifull, I love it. I will try it soon and let you guys know. At first sight, it seems this will do the job.