Re: How would I do this sub-select?
From: <dan_at_nospam.com>
Date: Wed, 14 Jan 2009 13:00:57 -0600
Message-ID: <496e3669$0$33214$815e3792_at_news.qwest.net>
>
> 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.
Date: Wed, 14 Jan 2009 13:00:57 -0600
Message-ID: <496e3669$0$33214$815e3792_at_news.qwest.net>
Bob Badour wrote:
> 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.
Missed the obvious! It works now. Thanks a bunch! Received on Wed Jan 14 2009 - 20:00:57 CET