assigning right order Order - 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

SQL Server 2005 Forums

Transact-SQL (2005)

assigning right order Order

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-03 : 09:30:40

my brains are tired :)

and ideas on this one :)

to populate the table.


create table purchase
(custID int
,OrderOrder int
,channel varchar(20)
)

insert into purchase
		   select 1123,1,'television' 
union ALL  select 1123,2,'television' 
union ALL  select 1123,3,'television' 
union ALL  select 1126,1,'television' 
union ALL  select 1126,2,'radio' 
union ALL  select 1120,1,'television' 
union ALL  select 1122,1,'television' 
union ALL  select 1122,2,'television' 
union ALL  select 1122,3,'internet' 
union ALL  select 1115,1,'internet' 
union ALL  select 1115,2,'television' 
union ALL  select 1115,3,'internet' 
union ALL  select 1115,4,'television'

and this is desired output


desired output
custID	orderOrder	        channel		r_n
1115	1			internet	1
1115	2			television	3
1115	3			internet	3
1115	4			television	3
1120	1			television	1
1122	1			television	1
1122	2			television	2
1122	3			internet	3
1123	1			television	1
1123	2			television	2
1123	3			television	2
1126	1			television	1
1126	2			radio		3


r_n  = row order
- 1 - first order
- 2 - all next orders with same channel as first order
- 3 - all next orders with different channel as first order

xpandre
Posting Yak Master

212 Posts

Posted - 2010-08-03 : 10:02:08

select a.*,1 from @purchase a where OrderOrder = 1
union
select c.*
,case when a.channel = c.channel then 2 else 3 end gg
from @purchase a outer apply
(select top 1 * from @purchase b where b.custID = a.custID and b.OrderOrder > a.OrderOrder order by b.orderorder) c
where c.custid is not null
order by 1

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-04 : 11:11:55

xpandre, thank you.

here is my solution:


;with cte_purchase (custID, orderorder, channel, neworder)
as
(   
      select
             p0.custID
            ,p0.orderorder
            ,p0.channel
            ,1 as neworder
      from purchase p0
      where p0.orderorder = 1

      union all
     
      select
            p1.custID
            ,p1.orderorder
            ,p1.channel
            ,case when p1.channel = cte_p.channel then 2 else 3 end as neworder
      from purchase as p1
            join cte_purchase as cte_p
            on p1.custID = cte_p.custID
            where
                  p1.orderorder > cte_p.orderorder
)

select
       c.custID
      ,c.orderorder
      ,c.channel
      ,max(c.neworder) as neworder
             from cte_purchase  as c


group by
       custID
      ,orderorder
      ,channel
order by custID, orderorder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources