But wait.  Your original query includes a subquery in the SELECT statement:

"SELECT PRC_ClientControlShortDesc, BCCODE, BICNUM, BUV,
Sum(Case DateName(Weekday,BDate) When 'Monday' Then BQUAN Else 0 End) as
DaySales1,
(Select Convert(varchar,BDate,101) from bil where DateName(Weekday,BDate)
= 'Monday' and DATEPART(yy, BDATE) = 2002 AND
DATEPART(week, BDATE)>=41 and DATEPART(week, BDATE)<= 41 group by bdate)
as Day1
..."

As you have found out, your subquery must be a *scalar* subquery; it must
only return *one* row for each outer query row.  This is because it is
providing a single value for a single column for each row output by the
SELECT statement. So, for any given row, I repeat my question, which Monday
would you have it use?

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Carolyn Friedberg [mailto:cfriedberg@c...]
Sent: Monday, November 04, 2002 5:20 PM
To: sql language
Subject: [sql_language] RE: Dates and subqueries.


In the case of more than one week, this line
DATEPART(week, BDATE)>=41 and DATEPART(week, BDATE)<= 41

would read somthing like this:

DATEPART(week, BDATE)>=41 and DATEPART(week, BDATE)<= 42.  In that case, I
would want more than one Monday date.