i have the following tables:
Actual         Optional
------         --------
4                 3
13                6
20                7
26                14
                  19
                  21
                  27
                  28
What i have to do is select :
1) all the values from "Actual" Table.
2) select values from "Optional" table if they form a consecutive series with "actual" table values
The expected result is:
Answer
------
4
13
20
26
3    --because it is consecutive to 4 (i.e 3=4-1) 
14   --14=13+1
19   --19=20-1
21   --21=20+1
27   --27=26+1
28   --this is the important case.28 is not consecutive to 26 but 27 
     --is consecutive to 26 and 26,27,28 together form a series.
I wrote a query using recursive cte but it is looping forever and fails after recursion reaches 100 levels. The problem i faced is 27 matches with 26, 28 matches with 27 and 27 with 28.again 28 with 27...(forever)
Here is the query i wrote:
with recurcte as
        (
        select num as one,num as two from actual
        union all
         select opt.num as one,cte.two as two 
         from recurcte cte join optional opt 
         on opt.num+1=cte.one or opt.num-1=cte.one
        )select * from recurcte
                A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.
The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.
What are CTEs? The CTE (common table expression), also known as the WITH clause, is an SQL feature that returns a temporary data set that can be used by another query. As it's a temporary result, it's not stored anywhere, but it still can be referenced like you would reference any other table.
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
;WITH Combined
     AS (SELECT 1 AS Actual, N
         FROM   (VALUES(4),
                       (13),
                       (20),
                       (26)) Actual(N)
         UNION ALL
         SELECT 0 AS Actual, N
         FROM   (VALUES(3),
                       (6),
                       (7),
                       (14),
                       (19),
                       (21),
                       (27),
                       (28)) Optional (N)),
     T1
     AS (SELECT *,
                N - DENSE_RANK() OVER (ORDER BY N) AS Grp
         FROM   Combined),
     T2
     AS (SELECT *,
                MAX(Actual) OVER (PARTITION BY Grp) AS HasActual
         FROM   T1)
SELECT DISTINCT N
FROM   T2
WHERE  HasActual = 1  
                        This CTE will give you the data you're looking for. Recursion is unnecessary for this.
declare @Actual table (i int)
declare @Optional table (i int)
insert into @Actual 
    select 4 union select 13 union select 20 union select 26
insert into @Optional 
    select 3 union select 6 union select 7 union select 14 union select 19
    union select 21 union select 27 union select 28
;with rownum as (
    select *, ROW_NUMBER() OVER (ORDER BY i) as 'RN'
    from (
        select
            i, 'A' as 'Source'
        from
            @Actual
        union
        select
            i, 'O'
        from
            @Optional
    ) a
)
select distinct
    d.i
from
    rownum a
    inner join rownum d
        on  a.i - d.i = a.rn - d.rn
where
    a.source = 'A'
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With