Home -> Usenet -> comp.databases.theory -> Re: How would I do this sub-select?

Re: How would I do this sub-select?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 14 Jan 2009 14:26:24 -0400
Message-ID: <496e2e53$0$5486$9a566e8b_at_news.aliant.net> 






Bob Badour wrote:


> dan_at_nospam.com wrote:
> 




>> Bob Badour wrote:


>>

>>> dan_at_nospam.com wrote:

>>>

>>>> Hi,

>>>>

>>>> I need to do "For each customer identified by Cust_ID sum last 90 

>>>> days of Sales from Monday for each Monday going back 365 days" and 

>>>> return 'Monday_Date', 'Cust_ID', 'Sales_Last_90_Days'.  I am going 

>>>> against MS SQL Server 2000.  Need some help with the SQL.

>>>>

>>>> Thanks!  -- dan

>>>

>>>

>>>

>>> Hi Dan,

>>>

>>> Don't you think you will learn more if you do your own homework?

>>

>>

>>

>> Here is what I have:

>>

>> SELECT Custid, Invoicedate AS Monday_Date,

>>

>>    (SELECT Sum(R_Sales) FROM Order_Header_Invoice AS T2 WHERE

>>     T2.Invoicedate <= T1.Invoicedate AND T2.Invoicedate >=

>>     DateAdd(day, -90, T1.Invoicedate) ) AS Sales_L90D

>>

>> FROM Order_Header_Invoice AS T1 WHERE ( DatePart(weekday, Invoicedate) 

>> = 2 AND Invoicedate >= DateAdd(day, -365, getdate()) )

>> GROUP BY Custid,  Invoicedate

>> ORDER BY Custid,  Invoicedate DESC

>>

>> but I get a "Timeout Expired" error.  Is this causing an infinite loop?

> 


> Check your parentheses.



Oops, never mind. Your parentheses are fine.



You considered how the dates are related between the parts of the query 
but not how the customer id's are related. That's causing a cartesion 
product of customers. It's not an infinite loop. It's finite but very large.
Received on Wed Jan 14 2009 - 19:26:24 CET












Original text of this message