Help with SQL statement

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.

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

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.
;)

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

Code:

---

WHERE YEARWEEK between 200510 AND 200602

---

good solution...! (this is off the top of my head.. it should :rolleyes: 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

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.

Quote:

---

Originally Posted by Static

good solution...! (this is off the top of my head.. it should :rolleyes: 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.

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

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

Quote:

---

Originally Posted by DKenny

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.
;)

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.

Code:

---

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

---

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

CAST!! forgot about that... ;)

Quote:

---

Originally Posted by 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.

Code:

---

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

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

Quote:

---

Originally Posted by DKenny

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.

Quote:

---

Originally Posted by D-niss

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.

Quote:

---

Originally Posted by D-niss

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.

Quote:

---

Originally Posted by Static

CAST!! forgot about that... ;)

---

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

Quote:

---

Originally Posted by DKenny

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.