# How to determine the most common intervals between the numbers within a draw

Hello,

I collected all lottery data, basically I have DrawNumber, DrawDate, Ball1, Ball2, Ball3, Ball4, Ball5 and Ball6. @Jason_A_Long helped me to retrieve most/least frequent numbers and the gaps of numbers each draw date. I would like to know if there is a way to determine most common intervals between numbers within a draw?

Here is the sample query of the gaps:

``````   SELECT
x.DrawDate,
x.Has5,
DrawlsBetween = x.Has5 - LAG(x.Has5, 1, 0) OVER (ORDER BY x.DrawDate) -1
FROM
(
SELECT
wn.DrawDate,
Has5 = CASE WHEN 5 IN (wn.[Ball1], wn.[Ball2], wn.[Ball3], wn.[Ball4], wn.[Ball5], wn.[Ball6]) THEN ROW_NUMBER() OVER (ORDER BY wn.DrawDate) END
FROM
[LuckyNumbers].[dbo].[SuperLoto] wn
) x
WHERE
x.Has5 IS NOT NULL;``````

Is this what you're looking for?

``````--=======================================================================
-- some test data...IF OBJECT_ID('tempdb..#WinningNumbers', 'U') IS NOT NULL
DROP TABLE #WinningNumbers;

CREATE TABLE #WinningNumbers (
DrawDate DATE NOT NULL,
Num_1 INT NOT NULL,
Num_2 INT NOT NULL,
Num_3 INT NOT NULL,
Num_4 INT NOT NULL,
Num_5 INT NOT NULL,
Num_6 INT NOT NULL
);

WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
cte_Tally (n) AS (
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b
),
cte_GetNumbers AS (
SELECT
t.n,
Position = DENSE_RANK() OVER (PARTITION BY t.n ORDER BY n.Number),
n.Number
FROM
cte_Tally t
CROSS APPLY ( VALUES -- generate 10 numbers to increase the likelyhood of getting 6 unique numbers.
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1)
) n (Number)
)
INSERT #WinningNumbers (DrawDate, Num_1, Num_2, Num_3, Num_4, Num_5, Num_6)
SELECT
Num_1 = MAX(CASE WHEN gn.Position = 1 THEN gn.Number END),
Num_2 = MAX(CASE WHEN gn.Position = 2 THEN gn.Number END),
Num_3 = MAX(CASE WHEN gn.Position = 3 THEN gn.Number END),
Num_4 = MAX(CASE WHEN gn.Position = 4 THEN gn.Number END),
Num_5 = MAX(CASE WHEN gn.Position = 5 THEN gn.Number END),
Num_6 = MAX(CASE WHEN gn.Position = 6 THEN gn.Number END)
FROM
cte_GetNumbers gn
GROUP BY
gn.n;

-- SELECT * FROM #WinningNumbers wn;
--=======================================================================
-- the solution...

WITH
cte_WeeksGap AS (
SELECT
upv.Num,
WeeksGap = DATEDIFF(wk, LAG(wn.DrawDate, 1) OVER (PARTITION BY upv.Num ORDER BY wn.DrawDate), wn.DrawDate)
FROM
#WinningNumbers wn
CROSS APPLY ( VALUES (wn.Num_1), (wn.Num_2), (wn.Num_3), (wn.Num_4), (wn.Num_5), (wn.Num_6) ) upv (Num)
)
SELECT
wg.Num,
AvgWeeksGap = AVG(wg.WeeksGap)
FROM
cte_WeeksGap wg
GROUP BY
wg.Num;``````

I will try and let you know @Jason_A_Long