Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2004-12-19 : 13:54:47
|
| Here is my question:I have to calculate the average yearly spend on mobile services of all customers in their first contract year. A customer is defined by Account Number, but can possess many handsets (for example, a corporate company).We have historical invoice data from Nov 2003 to date. Therefore, I require all the invoices for all the customers that joined in November (1-30) or December (1-to date). The invoices I have to tally HAVE to fall into the first contract year, and not include the 13th month (therefore, cannot say something like [SELECT SUM(invoices) WHERE ConnectionDate BETWEEN November and December 03 AND last InvoiceDate BETWEEN November and December 04)The data looks something like this:AccNo InvNo InvDate InvTotal ConnectDate TerminateA412613 123 3/12/2004 112374.5600 Nov 1, 2003 Oct 23, 2004D128070 1234 1/12/2004 2539.2000 Dec 12, 2003 Null9468427 1235 12/03/2004 96246.1100 April 3, 2003 NullD128070 1234 1/12/2004 32567.0000 Dec 12, 2003 NullD128070 12356 24/12/2003 2341.0000 Dec 12, 2003 Null8965266 3456 13/04/2004 5431096.3300 Dec 01, 2001 Null8965266 3457 20/04/2004 7298461.2100 Dec 01, 2001 Null[/size=2][/size=1]Those of interest I have highlighted in Blue. How can I word a query to add up the customer invoice totals for the first 12 months since their contract began?The final result is to look like this:Average Spend per Customer for First Contract Year£235,486.8900ThanksHearty head pats |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-19 : 17:31:37
|
| I'm probably making this too simple. Don't you just want where DATEDIFF(MM,ConnectDate,InvDate) <= 12?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-12-21 : 03:35:53
|
| Morning!!!Weheey, nearly christmas! Anyway, back to business, I did think of that, but then I will get the invoices from the customers that may have already disconnected. But then, I thought I could always select a subset of data, and get only those that have not disconnected, or have a termination date that is greater than a year (datediff(yy, Connection_date, Terminate_date)>=1), so basically, your solution would then work! So thanks mate, and have a super dooper christmas!Hearty head pats |
|
|
|
|
|
|
|
|
Average Spend Total with date conditions