monthly report for sale history

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

monthly report for sale history

Author

Topic

xtreme416
Starting Member

2 Posts

Posted - 2011-01-17 : 21:20:32

Hi guys,

I'm wondering what's the best way to generate a report by month and year (say every month from 2009 to 2011) for number of sales, but only for those who have placed an order within 12 months of the report period.

This is somewhat of a loyalty report. I'd like to know for any given period how many customers have placed an order with 12 months.

I have the following code, but it runs an empty table! If I take out the HAVING clause, I simply get the number of transactions for the period. Any suggestions?

DECLARE @myResults table (
[Year] int,
[Month] int,
[ActiveCustomers] int
)

DECLARE @intervalDate datetime
Set @intervalDate = '2010-11-01'

While @intervalDate < GETDATE()
BEGIN

WITH s AS ( SELECT customer_id, orderDate FROM Orders )

INSERT into @myResults
SELECT YEAR(@intervalDate), Month(@intervalDate), count(distinct s.customer_id)
FROM Customers cux
LEFT OUTER JOIN s ON cux.customer_id = s.customer_id
HAVING DATEDIFF(month, min(s.orderDate), @intervalDate) <= 12

SET @intervalDate = DATEADD(month, 1, @intervalDate)
END

SELECT * from @myResults

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-18 : 03:48:35

I *think* this should do what you want but it's untested so no guarantees:

DECLARE @intervalDate datetime
Set @intervalDate = '2010-11-01'

SELECT YEAR(a.orderDate), Month(a.orderDate), count(distinct b.customer_id)
FROM Orders a
	INNER JOIN Orders b
		ON a.customer_id = b.customer_id 
			AND b.orderDate >= DATEADD(month, -12, a.orderDate)
WHERE a.orderDate >= DATEADD(month, -12, @intervalDate)
GROUP BY YEAR(a.orderDate), Month(a.orderDate)
ORDER BY YEAR(a.orderDate), Month(a.orderDate)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

xtreme416
Starting Member

2 Posts

Posted - 2011-01-18 : 10:01:17

thanks Lumbago. Your query returned some data, but I'm not sure it's the right data. For example, when I query the database for the number of customers who had an order in May 2010 (simple query), I get about 10,000. This is the same number I get with the query you provided in your post.

Maybe I didn't explain my needs properly. I'm interested in finding out, in any given month, how many of our customers have transactions in the past 12 months of that period.

So in May 2010, how many customers have transactions between May 2009 and May 2010. And in June 2010, how many of our customers have transactions between June 2009 and June 2010, etc.

I hope I didn't confuse things more :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources