dbTalk Databases Forums  

How would I do this sub-select?

Go Back dbTalk Databases Forums Databases comp.databases.theory How would I do this sub-select?

comp.databases.theory comp.databases.theory


Discuss How would I do this sub-select? in the comp.databases.theory forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
dan@nospam.com
 
Posts: n/a

Default How would I do this sub-select? - 01-14-2009 , 09:49 AM





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

Reply With Quote
  #2  
Old   
Bob Badour
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 10:08 AM





dan (AT) nospam (DOT) com wrote:

Quote:
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?


Reply With Quote
  #3  
Old   
dan@nospam.com
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 11:05 AM


Bob Badour wrote:
Quote:
dan (AT) nospam (DOT) 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?


Reply With Quote
  #4  
Old   
Bob Badour
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 11:56 AM


dan (AT) nospam (DOT) com wrote:

Quote:
Bob Badour wrote:

dan (AT) nospam (DOT) 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.


Reply With Quote
  #5  
Old   
dan@nospam.com
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 12:11 PM


Bob Badour wrote:
Quote:
dan (AT) nospam (DOT) com wrote:

Bob Badour wrote:

dan (AT) nospam (DOT) 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.
The parentheses are matched as far as I can tell. If they weren't, I
would an error.


Reply With Quote
  #6  
Old   
Bob Badour
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 12:26 PM


Bob Badour wrote:

Quote:
dan (AT) nospam (DOT) com wrote:

Bob Badour wrote:

dan (AT) nospam (DOT) 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.


Reply With Quote
  #7  
Old   
dan@nospam.com
 
Posts: n/a

Default Re: How would I do this sub-select? - 01-14-2009 , 01:00 PM


Bob Badour wrote:
Quote:
Bob Badour wrote:

dan (AT) nospam (DOT) com wrote:

Bob Badour wrote:

dan (AT) nospam (DOT) 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!


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top