btw, the reason you received the error is that a derved table in the from clause cannot see any other members of the from clause.

[font="Courier New"]

SELECT P.street_number_full AS StreetNumber, P.street_pre_direction...

take a look at this thread: http://www.sqlservercentral.com/Forums/Topic436572-338-1.aspx

re: Samuel Vella

yup, you're right -- 1 per month. i've been doing a rebuild/release all night and letters and digits are starting to run together. 😀

still, how does...

re: Samuel Vella

that query does not seem to only consider sellers who have sold at least 3 items in each of the past 3 months. have i misinterpreted the...

suggest you read this: Table Partitioning by Andy Warren[/url]

it doesn't sound that simple. you're going to need some intermediate summary to determine sales by month.

declare @startDate smalldatetime, @endDate smalldatetime

set @startDate = dateadd( month,-3,getdate() ) -- 3 months...

need to change it to something like this:

SELECT YEAR(OrderDate) as year, MONTH(OrderDate) as month, COUNT(DISTINCT CustomerID)...

aysegul (1/15/2008)

---

Hi. With your help I wrote this sql as below.

But it doesn't work as I want.

I want to filter by with dbo.fDetermineStartDate return value when

fDetermineEndDate is...

kipp (1/14/2008)

---

hey thanks... I am going to have to go with the Max function below, one question I would have is how can I put this in a loop to...

I suggest you try using a couple of user defined functions to replace the case statements.

create function dbo.fDetermineStartDate( @status int, @dueStartDate datetime, @actualStartDate datetime )

returns datetime

begin

return...

GSquared: With my biggest complaint nullified, I'm all for the numbers approach.

And it's just that for my usage (big decision support queries) a 50ms improvement isn't thrilling....

GSquared:

I must be missing something. Per your own tests, the Numbers-based string parser was 2-3 times faster than the While loop parser every time. Yet you say its speed doesn't...

ss2k5. as shown in my earlier examples, the numbers table isn't...

GSquared: totally agree on your points save one -- i don't think a numbers table is ever necessary. but i'll read your references since i'm sure i'll learn...

GSquared: I really like stuff like this, so I hope you're not annoyed with another reply.

But I don't think the numbers table is giving you enough bang...