Sequencing on a subset of data.

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 2005 Forums

Transact-SQL (2005)

Sequencing on a subset of data.

Author

Topic

dukey07
Starting Member

16 Posts

Posted - 2009-10-21 : 14:59:55

So my actual issue is a bit more complicated, but I tried to simplify it here to get to the heart of it.

Consider the following sample data;

Create Table #test (CustomerID int , OrderDate datetime)

Insert into #test(CustomerID, OrderDate)
Values(1, '09/09/09')
Insert into #test(CustomerID, OrderDate)
Values(1, '11/11/11')
Insert into #test(CustomerID, OrderDate)
Values(1, '10/10/10')
Insert into #test(CustomerID, OrderDate)
Values(2, '07/07/07')
Insert into #test(CustomerID, OrderDate)
Values(2, '08/08/08')
Insert into #test(CustomerID, OrderDate)
Values(3, '11/11/11')

I want to add a sequence field as follows, in order to join to another poorly designed table. I need the following result.

CustomerID, OrderDate, Sequence
1 09/09/2009 1
1 10/10/2010 2
1 11/11/2011 3
2 07/07/2007 1
2 08/08/2008 2
3 11/11/2011 1

So I can of course see how to do this with an ugly cursor and just iterate through each customer and create the sequence using an identity field in a temp table. I am hoping for a more set based approach, perhaps using a "numbers" table?

Thanks,
Dukey07

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-21 : 15:04:48

[code]select *,row_number() over(partition by CustomerID order by OrderDate)
from #test[/code]

dukey07
Starting Member

16 Posts

Posted - 2009-10-21 : 15:11:56

quote:
Originally posted by vijayisonly

select *,row_number() over(partition by CustomerID order by OrderDate)
from #test

This works like a champ in sql 2008. I can make this work jumping through a few hoops, but is there a sql 2000 answer? Apologies for lack of this information in the OP.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-21 : 15:22:59

ok...try this...

select e1.CustomerID,e1.OrderDate,
(select count(*) from #test as e2
          where e2.CustomerID = e1.CustomerID and e2.OrderDate <= e1.OrderDate) as myNo
from #test as e1
order by CustomerID,myNo

EDIT: This will be very slow, if the data you are dealing with is huge.

dukey07
Starting Member

16 Posts

Posted - 2009-10-21 : 15:41:57

quote:
Originally posted by vijayisonly

ok...try this...

select e1.CustomerID,e1.OrderDate,
(select count(*) from #test as e2
where e2.CustomerID = e1.CustomerID and e2.OrderDate <= e1.OrderDate) as myNo
from #test as e1
order by CustomerID,myNo

EDIT: This will be very slow, if the data you are dealing with is huge.

This works. Thanks! I am not concerned much about speed. rebuilding the tables with some better foreign key relationships, I just needed this to do the data transformation into the new table structures, so it is a one off.

Thanks again!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-21 : 15:52:54

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources