How to construct a query for annual revenue?

[fox12]

Hi Gurus,

I have a table containing data like followings:
cust_id amount startDate endDate
123 2345.00 12/01/2004 11/30/2005
123 2440.00 12/01/2005 11/30/2006
124 3660.00 05/01/2005 04/30/2006
125 234.00 11/01/2005 10/31/2006
126 654.00 02/01/2006 08/31/2006

The fiscal year starts on October 1 for each year. How can I construct a query to calculate the annual amount and the first quarter amount for 2006? The annual/quarter amount should be calcalted as follows: 1.) find the total months between "startDate" and "endDate"; 2.) get the average amount for each month by dividing "amount" by the total months (as in Step 1); 3.) add any amount if a month between 10/01/2005 and 12/31/2005. For instance, the customer whose Id is 123 has two months falling in the first quarter of 2006. Hence, I need to add [(amount/12) * 2] into the total. The customer with ID of 126 should not be counted in the first quarter amount, but should be in the annual amount.

Any idea on the annual amount and the quarterly amount? Appreciate so much for any of your help on this.

Fox12

 

[NoCoolHandle]

Fox..
What do you want the output to look like?

My guess is that a seach in BOL for "Cross-Tab Reports" might be a good starting point, but again, what do you want the results to look like?

Rob

 

[NoCoolHandle]

Also.. the datepart and dateadd funcitons might make your life much easier..

Search BOL for them also.. but
ie

select datepart(q,dateadd(m,3,getdate()))

would return a 2 indicating that this is the second quater..
that would simplify the select as you wouldn't need to do the between.

It might also be usefull for the output.

Rob

 

[fox12]

NoCoolHandle,

Here is the output I want:

Annual Amount --
From Customer ID 123: (2345 / 12 ) * 2 + (2440 / 12) * 10
From Customer ID 124: (3660 / 12) * 7
From Customer ID 125: (234 / 12) * 11
From Customer ID 126: (654 / 8) * 8

1st Quarter Amount (there are 3 months in a quarter) --
From Customer ID 123: (2345 / 12 ) * 2 + (2440 / 12) * 1
From Customer ID 124: (3660 / 12) * 3
From Customer ID 125: (234 / 12) * 2