Help with SQL statement

[D-niss]

I need help writing a sql stmt. Here’s what I’m trying to do:

See attachement pic for tables

I need to get the sum of sales and cost between two week # (say between week 48 of 2005 and week 2 of 2006), grouped by dept.
I know I have to look up the dates in the calendar table, in order to use a bigger than and lesser than ..date.
I didn’t put all the weeks in this example, but the table has 80,000 lines, so they’re all there. Same with the calendar, I have all dates.

Can some of you sql guru give me a hand? Can’t be that difficult but I’m stuck with this and nothing I’ve tried works.

Many thanks.

[Static]

Why do u need the dates? can u use the week numbers?
SELECT SUM(SALES) FROM tblDATA WHERE [Week] Between 48 and 52

[D-niss]

How about between week 48 and week 2 in 2006?

Week 2 is after week 48 according to the dates. But don't forget I also
have a week 2 in the year 2005, I can't show here all the 80,000 lines.

[DKenny]

Step 1/
Create a view on top of your DATA table with a calculated column, lets call it YEARWEEK, that is a concatenation of YEAR and WEEK (including a zero before single digit months).
This field will have data as follow
200501
200502
200503
...etc...
200512
200601
..etc...

Step 2/
Your WHERE clause now becomes

WHERE YEARWEEK between 200510 AND 200602

[Static]

good solution...! (this is off the top of my head.. it should work in access but I dont know what DB u are using)


SELECT YM, SUM(SALES) FROM (SELECT FISC_YEAR & FORMAT([WEEK],"0#") as YM, SALES FROM tblDATA) WHERE YM Between 200548 and 200602

[D-niss]

Interesting approach. Is there anyway I could do this in one sql statement?
Actually, I have to do this in one pass.
Can you give me an example on how I would write this?

I really s*** at sql, sorry for asking so much.

[D-niss]

good solution...! (this is off the top of my head.. it should work in access but I dont know what DB u are using)


SELECT YM, SUM(SALES) FROM (SELECT FISC_YEAR & FORMAT([WEEK],"0#") as YM, SALES FROM tblDATA) WHERE YM Between 200548 and 200602

I'm using ADO to connect to an oracle database. So I guess it's pretty much generic sql.

[DKenny]

Do you have the ability/Authority to create a VIEW on the Oracle DB?

[D-niss]

Thanks a bunch guys for your help, I will try it out. If anyone has other syntax other than using concatenate, please post it.

[D-niss]

Do you have the ability/Authority to create a VIEW on the Oracle DB?

I could ask the db admin to do it, but since I know this can be done from my end in one stmt, I find that solution more attractive. This way, I wouldn't have to bug him and wait for him.

[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

[Static]

i think concat would be the way to do it...


SELECT YM, SUM(SALES) FROM (SELECT CONCAT(FISC_YEAR,WEEK) as YM, SALES FROM tblDATA) WHERE YM Between 200548 and 200602

no wait.. that wont format the weeks to 01 ... hmmm

[Static]

CAST!! forgot about that...

[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]

I would want to avoide a Sub-SELECT as you cannot pass it a WHERE statement - therfore a lot more user-side processing.

[D-niss]

I would want to avoide a Sub-SELECT as you cannot pass it a WHERE statement - therfore a lot more user-side processing.

ahh ok, I see your point. I'm aiming for speed here, since I have to do this quite a number of time.

[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!! forgot about that...

lol, cool. It's not to late. Any help I get, even slightly wrong, points me in the right direction.

[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.