banding by year - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

banding by year

Author

Topic

Davin21
Starting Member

3 Posts

Posted - 2010-08-06 : 05:29:29

Hi,
I am looking to categorise people on a database into new / reactivated / consecutive purchasers where they are defined as:
new purchasers have only purchased in the current year

reactivated have purchased in the current year, not the most recent year but have also purchased in the year before that (and any other prior years) - i.e have bought in 2010, 2008 and anything else before that

consecutive - ideally the number of consecutive years in which they have purchased.

I would be using the date range of each year being defined as 1st Jan - 31st december), in SQL server 2005 and do not have permission to create functions so a query would be preferably if possible!!

Many thanks,
Davin

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 06:02:14

Post some sample data & your desired o/p .

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Davin21
Starting Member

3 Posts

Posted - 2010-08-06 : 06:16:18

Sample Data:

customer id | date_purchase
1005 21/03/2010
1005 21/02/2008
1005 21/06/1999
1221 01/09/2010
1654 01/01/2010
1654 05/12/2009
1654 06/08/2008
1654 05/04/2007

Output:
customer id | type
1005 reactivated customer
1221 new customer
1654 4 yrs consecutive customer

Alternatively if this is not possible I would like:

customer id | bought 2010 | bought 2009 | bought 2008 | bought 2007
1005 Yes No Yes No
1221 Yes No No No
1654 Yes Yes Yes Yes

My actual data is around 15 million rows, so can easily post more sample data if required :)

Thanks,
Davin.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources