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

VALUES   (1,    1,  '2013-09-01', 'Rasmus')
,(2,    1,  '2013-11-01', 'Christian')
,(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  *

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'
INNER JOIN  (
SELECT  A.CUSTOMER_ID
GROUP BY A.CUSTOMER_ID

UNION ALL

,T10.CUSTOMER_ID
,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
,CASE
WHEN DATEDIFF(DAY, T6.MAX_COMMISSION_DATE, T5.LEAD_CREATED_DATE) > 365      THEN 'YES'
ELSE 'NO'
END AS COMMISSION
,CASE
ELSE NULL
END AS COMMISSION_DATE
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
) AS T10
WHERE   RN = 1

)
SELECT  *
FROM    COMMISSION_CALCULATION;
``````

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

values (1,    1,  '2013-09-01', 'rasmus')
,(2,    1,  '2013-11-01', 'christian')
,(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
,customer_id
,salesperson_name
)
,cte
as
(
select rn
,customer_id
,salesperson_name
,'yes' as commission_result
from rownum
where rn = 1

union all

select r.rn
,r.customer_id
,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
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)
else c.commission_window_start
end
end as commission_window_start

from rownum r
inner join cte c
on(r.rn = c.rn+1)
)