Use recursive CTE to handle date logic

Question

At work, one of my tasks is to calculate the commission for sales staff. One rule was more complex than the other.

The two sales teams A and B work together to sell different products. Team A can send commands to team B. The same client can send several times. The first time a client (for example, 1st lead) * sends a commission, it is paid to the seller in team A, which created the leading position. Now the client is "blocked" for the next 365 days (from the date of creation of date 1). This means that no one can get additional commission for this client during this period by sending additional leaders (for example, Lead 2 and 3 does not receive commissions). After 365 days. A new guide can be created and receive a commission (for example, guide 4). Then the client is again blocked for 365 days, counting from the day that output 4 was created. Therefore, management 5 does not receive a commission. The tricky part is resetting the date for 365 days.

'* Link to tables #LEADS and #DISERED.

I solved the problem in tSQL with a cursor, but I am wondering if recursive CTE can be used instead. I made several attempts which are best inserted below. The problem with my solution is that I refer to the recursive table more than once. I tried to fix this problem with embedding CTE inside CTE. That is not allowed. I tried using a temporary table inside the CTE, which is also not allowed. I tried several times to recode the recursive part of the CTE, so that the recursive table refers only once, but then I can not 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 1

I made some assumptions where your description does not make full sense, as written, but below reaches the 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;