SQL outer join thing i think

[davecarrera]

Ver new to SQL and trying to get this right.

I am using Reporting servcies 2000 to try and make a week by week total report.

I have this sql at the moment cobbled together from various websites.

SELECT customer AS Cust,
CASE WHEN dated >= '09/10/06' AND dated <= '09/16/06' THEN (SUM(amount)) ELSE 0 END AS Week0,
CASE WHEN dated >= '09/17/06' AND dated <= '09/23/06' THEN (SUM(amount)) ELSE 0 END AS Week1,
CASE WHEN dated >= '09/24/06' AND dated <= '09/30/06' THEN (SUM(amount)) ELSE 0 END AS Week2,
CASE WHEN dated >= '10/01/06' AND dated <= '10/07/06' THEN (SUM(amount)) ELSE 0 END AS Week3,
ISNULL(
( SELECT customer as cust2,
SUM(Week0) AS [Week of 16/09/06],
SUM(Week1) AS [Week of 23/09/06],
SUM(Week2) AS [Week of 30/09/06],
SUM(Week3) AS [Week of 07/10/06]

FROM table
WHERE (dated >= CONVERT(DATETIME, '2006-09-10 00:00:00', 102)) AND (dated <= CONVERT(DATETIME, '2006-10-07 00:00:00', 102))
GROUP BY customer, dated, kind
HAVING (kind = 'INV') OR
(kind = 'CRN')

),0) AS DETAIL


FROM table
WHERE (dated >= CONVERT(DATETIME, '2006-09-10 00:00:00', 102)) AND (dated <= CONVERT(DATETIME, '2006-10-07 00:00:00', 102))
GROUP BY customer, dated, kind
HAVING (kind = 'INV') OR
(kind = 'CRN')
ORDER BY customer

But all i get is errors saying week0 not a colum.

I maus have something wrong here so i ask for someone who is cleverer than i with sql to have a look a guide me gentley towards an answer.

Many Kind Regards

Dave

 

[LKBrwnDBA]

1) Change your CASE statements to:

SUM(CASE WHEN dated >= '09/10/06' AND dated <= '09/16/06' THEN amount ELSE 0 END) AS Week0,

2) You cannot refer to a column 'alias' within the same SQL statement:

SUM(Week0) AS [Week of 16/09/06],

3) Your GROUP BY statement is incorrect.

4) Everything from " ),0) AS DETAIL" to the end is invalid statement.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[davecarrera]

Thanks LKBrwnDBA for the help, fixed the by week issue.

Now i have a visual thing to try and fix

I get my report by week as expected, but each week is not the SUM of its contribution.

What i mean is say customer number 00001 had 5 trades in week0, 4 trades in week1, and so on i get a list show each individual trade for the week rather than the total for that week.

It sort of looks like this

Customer Week0 Week1 Week2 Week3
00001 34.00 0.00 0.00 0.00
00001 23.00 0.00 0.00 0.00
00001 0.00 1.98 0.00 0.00
00001 0.00 0.00 23.00 0.00
00001 0.00 0.00 12.00 0.00
00001 0.00 0.00 0.00 34.00
00001 0.00 0.00 0.00 12.00
00001 0.00 0.00 0.00 9.00

What i was looking for is something like this

Customer Week0 Week1 Week2 Week3
00001 57.00 1.98 35.00 55.00
next customer
next customer
and so on

Any ideas what can be done ???

Many thanks for the helps so far

Dave

 

[PHV]

You wanted this ?
SELECT customer AS Cust
, SUM(CASE WHEN dated >= '09/10/06' AND dated <= '09/16/06' THEN amount ELSE 0 END) AS Week0
, SUM(CASE WHEN dated >= '09/17/06' AND dated <= '09/23/06' THEN amount ELSE 0 END) AS Week1
, SUM(CASE WHEN dated >= '09/24/06' AND dated <= '09/30/06' THEN amount ELSE 0 END) AS Week2
, SUM(CASE WHEN dated >= '10/01/06' AND dated <= '10/07/06' THEN amount ELSE 0 END) AS Week3
FROM table
WHERE dated >= CONVERT(DATETIME, '2006-09-10 00:00:00', 102)
AND dated <= CONVERT(DATETIME, '2006-10-07 00:00:00', 102)
AND kind IN ('INV','CRN')
GROUP BY customer
ORDER BY customer

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

if dated is a DATETIME datatype, you do not need to explicitly convert date strings for comparison

so change this --

WHERE dated >= CONVERT(DATETIME, '2006-09-10 00:00:00', 102)
AND dated <= CONVERT(DATETIME, '2006-10-07 00:00:00', 102)

to this --

WHERE dated BETWEEN '2006-09-10' AND '2006-10-07'


r937.com | rudy.ca

 

[davecarrera]

Sucess

I now have the mechanics of the report working with many thanks to the help of this forums memebers

Ok now to go a step further.

See the fixed dates in my case references, i would like to try and make these dynamic.

I would like the user to enter a week ending date, which is ALWAYS a saturday in our case, and then do my date selections starting with the users end date then -7 days for each of the week.

So if the user enters 07/10/06

My dates become

select dated where dated >=@userdate -7 or <=@userdate as week3, dated >=@userdate -14 or <=@userdate-7 as week2, dated >=@userdate -21 or <=@userdate-14 as week1, dated >=@userdate -28 or <=@userdate-21 as week0

Dose that make sence, so i enter one date and caculate the start and end date minusing 7 days for each week.

I have played around with DATEADD() but to no joy.

Any help is very very much appreciated

Kind Regards

Dave