Recursive Self Query With Associated Tables (SQL Server)

Question

Given the following structure and data, I am looking for joins by customer where a manufacturer has changed:

create table #sale (
    saleid int,
    customerid int,
    productid int,
    saledate date
);
create table #product (
    productId int,
    name varchar(max),
    manufacturerId int
);
create table #manufacturer (
    manufacturerid int,
    name varchar(max)
)

insert into #manufacturer values (1, 'Manufacturer 1');
insert into #manufacturer values (2, 'Manufacturer 2');

insert into #product values (1, 'Product A', 1);
insert into #product values (2, 'Product B', 1);
insert into #product values (3, 'Product C', 2);

insert into #sale values (1, 101, 1, '2013-01-01');
insert into #sale values (2, 101, 2, '2015-04-01');
insert into #sale values (3, 102, 3, '2013-03-01');
insert into #sale values (4, 102, 3, '2015-01-01');
insert into #sale values (5, 103, 1, '2013-01-01');
insert into #sale values (6, 103, 3, '2015-06-01');
insert into #sale values (7, 102, 1, '2015-06-01');

In this scenario, two customers have switched to products with different manufacturers and the ideal results look like this:

customerid  previous manufacturer   new manufacturer    date
102         Manufacturer 2          Manufacturer 1      6/1/2015
103         Manufacturer 1          Manufacturer 2      6/1/2015

I've been trying to use a CTE, but have not found success. Appreciate and insight or guidance.

Update - I could live with expanding horizontally a certain number of joins on customerid to show different manufacturers and dates (5-10 joins). That one is a lot easier to do for me than trying to UNION ALL with a CTE.

Thanks!

And what if a customer switches manufacturer more than once. What is the expected result? Also, what SQL have you tried so far? — sstan
– sstan, Commented Aug 23, 2015 at 23:45
@sstan If they switch more than once, it would show up as another record. I would characterize my SQL thus far as worthless. I'm thinking about doing several derived tables and joining on them to show all the different manufacturers and dates by customer(with up to 10 different manufacturer changes, eg 102 | Manufacturer 1 | 6/1/2015 | Manufacturer 2 | 1/1/2015). It's not ideal, but the recursive portion is challenging me since it's not really my forte. — kakridge
– kakridge, Commented Aug 24, 2015 at 0:00

sstan · Accepted Answer · 2015-08-24 00:17:20Z

3

Here is an alternative using LAG:

select customerid,
       [previous manufacturer],
       [new manufacturer],
       saledate
from (
  select s.customerid, 
         m.name as [new manufacturer],
         s.saledate,
         lag(m.name) over (partition by s.customerid order by s.saledate) as [previous manufacturer],
         case when 
           lag(m.manufacturerId) over (partition by s.customerid order by s.saledate) is not null 
           and lag(m.manufacturerId) over (partition by s.customerid order by s.saledate) <> m.manufacturerId
         then 1 else 0
         end as is_manufacturer_change
  from sale s
  join product p on p.productid = s.productid
  join manufacturer m on m.manufacturerid = p.manufacturerid) x
where x.is_manufacturer_change = 1
order by customerid, saledate

SQLFiddle

answered Aug 24, 2015 at 0:17

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

LAG() is definitely the better choice.

kakridge Over a year ago

Thanks for this answer sstan. I had already accepted the other answer, but I gave you an upvote. Was excited to try out LAG and then found it isn't supported in SQLAzure... bummer.

sstan Over a year ago

Sorry to hear that. Glad you also got a "lagless" solution then :)

M.Ali · Accepted Answer · 2015-08-24 00:14:21Z

Query

WITH X AS 
 (
  SELECT s.*
        ,M.manufacturerid
        ,m.name
        ,ROW_NUMBER() OVER (PARTITION BY s.CustomerID
                            ORDER BY saledate ASC) rn 
  FROM #sale s 
  INNER JOIN #product p      ON s.productid = p.productId
  INNER JOIN #manufacturer m ON p.manufacturerId = m.manufacturerid 
)
SELECT xx.customerid
       ,xy.name        [previous manufacturer]
       ,xx.name        [new manufacturer]
       ,xx.saledate [Date]
FROM x xy
INNER JOIN x xx ON xx.customerid = xy.customerid
               AND xx.rn = xy.rn + 1
               AND xx.manufacturerid <> xy.manufacturerid

Result:

╔════════════╦═══════════════════════╦══════════════════╦════════════╗
║ customerid ║ previous manufacturer ║ new manufacturer ║    Date    ║
╠════════════╬═══════════════════════╬══════════════════╬════════════╣
║        102 ║ Manufacturer 2        ║ Manufacturer 1   ║ 2015-06-01 ║
║        103 ║ Manufacturer 1        ║ Manufacturer 2   ║ 2015-06-01 ║
╚════════════╩═══════════════════════╩══════════════════╩════════════╝

Collectives™ on Stack Overflow

Recursive Self Query With Associated Tables (SQL Server)

2 Answers 2

3 Comments

Query

Result:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Query

Result:

Comments

Your Answer

Sign up or log in

Post as a guest

Related