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