Knud Back Knud Back - 18 days ago 5
SQL Question

Use recursive CTE to handle date logic

At work, one of my assignments is to calculate commission to the sales staff. One rule has been more challenging than others have. The first lead on a customer pays gets all commission the next 365 days on that customer. A second lead on the same customer gets no commission if the lead is created within 365 days of the first lead. If the second lead is created after 365 days of the first lead the customer gets commission. The second lead gets all commission the next 365 days. After that the pattern is repeated.

I have solved the problem in tSQL using a cursor, but I wonder if it was possible to use a recursive CTE instead. I have made several attempts the best one is pasted in below. The problem with my solution is, that I refer to the recursive table more than once. I have tried to fix that problem with nesting a CTE inside a CTE. That’s is not allowed. I have tried using a temporary table inside the CTE that is not allowed either. I tried several times to recode the recursive part of the CTE so that the recursive table is referenced only once, but then I am not able to get the logic to work.

I am using SQL 2008

IF OBJECT_ID('tempdb.dbo.#LEADS', 'U') IS NOT NULL
DROP TABLE #LEADS;

CREATE TABLE #LEADS (LEAD_ID INT, CUSTOMER_ID INT, LEAD_CREATED_DATE DATETIME, SALESPERSON_NAME varchar(20))
INSERT INTO #LEADS
VALUES (1, 1, '2013-09-01', 'Rasmus')
,(2, 1, '2013-11-01', 'Christian')
,(3, 1, '2014-01-01', 'Nadja')
,(4, 1, '2014-12-24', 'Roar')
,(5, 1, '2015-12-01', 'Kristian')
,(6, 2, '2014-01-05', 'Knud')
,(7, 2, '2015-01-02', 'Rasmus')
,(8, 2, '2015-01-08', 'Roar')
,(9, 2, '2016-02-05', 'Kristian')
,(10, 2, '2016-03-05', 'Casper')

SELECT *
FROM #LEADS;

IF OBJECT_ID('tempdb.dbo.#DISERED_RESULT', 'U') IS NOT NULL
DROP TABLE #DISERED_RESULT;

CREATE TABLE #DISERED_RESULT (LEAD_ID INT, DESIRED_COMMISION_RESULT CHAR(3))
INSERT INTO #DISERED_RESULT
VALUES (1, 'YES')
,(2, 'NO')
,(3, 'NO')
,(4, 'YES')
,(5, 'NO')
,(6, 'YES')
,(7, 'NO')
,(8, 'YES')
,(9, 'YES')
,(10, 'NO')

SELECT *
FROM #DISERED_RESULT;

WITH COMMISSION_CALCULATION AS
(
SELECT T1.*
,COMMISSION = 'YES'
,MIN_LEAD_CREATED_DATE AS COMMISSION_DATE
FROM #LEADS AS T1
INNER JOIN (
SELECT A.CUSTOMER_ID
,MIN(A.LEAD_CREATED_DATE) AS MIN_LEAD_CREATED_DATE
FROM #LEADS AS A
GROUP BY A.CUSTOMER_ID
) AS T2 ON T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T1.LEAD_CREATED_DATE = T2.MIN_LEAD_CREATED_DATE

UNION ALL

SELECT T10.LEAD_ID
,T10.CUSTOMER_ID
,T10.LEAD_CREATED_DATE
,T10.SALESPERSON_NAME
,T10.COMMISSION
,T10.COMMISSION_DATE
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T5.CUSTOMER_ID ORDER BY T5.LEAD_CREATED_DATE ASC) AS RN
,T5.*
,T6.MAX_COMMISSION_DATE
,DATEDIFF(DAY, T6.MAX_COMMISSION_DATE, T5.LEAD_CREATED_DATE) AS ANTAL_DAGE_SIDEN_SIDSTE_COMMISSION
,CASE
WHEN DATEDIFF(DAY, T6.MAX_COMMISSION_DATE, T5.LEAD_CREATED_DATE) > 365 THEN 'YES'
ELSE 'NO'
END AS COMMISSION
,CASE
WHEN DATEDIFF(DAY, T6.MAX_COMMISSION_DATE, T5.LEAD_CREATED_DATE) > 365 THEN T5.LEAD_CREATED_DATE
ELSE NULL
END AS COMMISSION_DATE
FROM #LEADS AS T5
INNER JOIN (SELECT T4.CUSTOMER_ID
,MAX(T4.COMMISSION_DATE) AS MAX_COMMISSION_DATE
FROM COMMISSION_CALCULATION AS T4
GROUP BY T4.CUSTOMER_ID) AS T6 ON T5.CUSTOMER_ID = T6.CUSTOMER_ID
WHERE T5.LEAD_ID NOT IN (SELECT LEAD_ID FROM COMMISSION_CALCULATION)
) AS T10
WHERE RN = 1


)
SELECT *
FROM COMMISSION_CALCULATION;

Answer

I have made some assumptions where your description does not fully make sense as written, but the below achieves your desired result:

if object_id('tempdb.dbo.#leads', 'u') is not null
drop table #leads;

create table #leads (lead_id int, customer_id int, lead_created_date datetime, salesperson_name varchar(20))
insert into #leads
values (1,    1,  '2013-09-01', 'rasmus')
      ,(2,    1,  '2013-11-01', 'christian')
      ,(3,    1,  '2014-01-01', 'nadja')
      ,(4,    1,  '2014-12-24', 'roar')
      ,(5,    1,  '2015-12-01', 'kristian')
      ,(6,    2,  '2014-01-05', 'knud')
      ,(7,    2,  '2015-01-02', 'rasmus')
      ,(8,    2,  '2015-01-08', 'roar')
      ,(9,    2,  '2016-02-05', 'kristian')
      ,(10,   2,  '2016-03-05', 'casper')

if object_id('tempdb.dbo.#disered_result', 'u') is not null
drop table #disered_result;

create table #disered_result (lead_id int, desired_commision_result char(3))
insert into #disered_result
values (1, 'yes'),(2, 'no'),(3, 'no'),(4, 'yes'),(5, 'no'),(6, 'yes'),(7, 'no'),(8, 'yes'),(9, 'yes'),(10, 'no')

with rownum
as
(
    select row_number() over (order by customer_id, lead_created_date) as rn                                -- This is to ensure an incremantal ordering id
            ,lead_id
            ,customer_id
            ,lead_created_date
            ,salesperson_name
    from #leads
)
,cte
as
(
    select rn
            ,lead_id
            ,customer_id
            ,lead_created_date
            ,salesperson_name
            ,'yes' as commission_result
            ,lead_created_date as commission_window_start
    from rownum
    where rn = 1

    union all

    select r.rn
            ,r.lead_id
            ,r.customer_id
            ,r.lead_created_date
            ,r.salesperson_name

            ,case when r.customer_id <> c.customer_id       -- If the customer ID has changed, we are at a new commission window.
                then 'yes'
                else case when r.lead_created_date > dateadd(d,365,c.commission_window_start)   -- This assumes the window is 365 days and not one year (ie. Leap years don't add a day)
                        then 'yes'
                        else 'no'
                        end
                end as commission_result

            ,case when r.customer_id <> c.customer_id
                then r.lead_created_date
                else case when r.lead_created_date > dateadd(d,365,c.commission_window_start)   -- This assumes the window is 365 days and not one year (ie. Leap years don't add a day)
                        then r.lead_created_date
                        else c.commission_window_start
                        end
                end as commission_window_start

    from rownum r
        inner join cte c
            on(r.rn = c.rn+1)
)
select lead_id
        ,commission_result
from cte
order by customer_id
        ,lead_created_date;
Comments