SQLTeam.com | Weblogs | Forums

Trying to replicate a value


#1

I've got an enormous data set something on the order of 300 million rows 15+years of customer service agreement transactions and essentially what I'm trying to do get at is the original sale store.

So when the agreement is first set up the store it was set up at goes in as the processing store, when they make subsquent payments it uses a different cost centre

so something like
product cover type payment cycle store
laptop 1 year agreement first payment store 1234
laptop 1 year agreement second payment store 1111
laptop 1 year agreement third payment store 1111
laptop 1 year agreement fourth payment store 1111
laptop 1 year agreement fifth payment store 1111
and so on

How could I make the following table?

product cover type payment cycle store original store
laptop 1 year agreement first payment store 1234 1234
laptop 1 year agreement second payment store 1111 1234
laptop 1 year agreement third payment store 1111 1234
laptop 1 year agreement fourth payment store 1111 1234
laptop 1 year agreement fifth payment store 1111 1234


#2
select a.product, a.cover, a.type, a.[payment cycle], f.store
from   agreement a
outer apply
(
     select x.store
     from   agreement x
     where  x.product = a.product
     and    x.[payment cycle] = 'first payment'
) f