Finding Matching Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Finding Matching Numbers

Author

Topic

jamez100
Starting Member

2 Posts

Posted - 2013-12-07 : 17:23:36

Hello,

I understand there have been a number of posts along the same lines, but I haven't been able to re-use the code to fix my problem.

I have relatively basic sql skills that include selecting, inserting, updating records, etc, but have recently started a personal lottery number checking project.

I have a database of all 1866 Uk lottery draws, containing all the all draw results. So far, I have used the following code to check if a 6 number combination has ever been drawn, which works :-)

SELECT *
FROM AllResultsTo091113$
WHERE (N1 = @TextBoxN11) AND (N2 = @TextBoxN21) AND (N3 = @TextBoxN31) AND (N4 = @TextBoxN41) AND (N5 = @TextBoxN51) AND (N6 = @TextBoxN61)

To further enhance this project I would also like check if the my input of 6 numbers, also matches to any previous draw results for 5 numbers and the bonus ball, or five numbers, or 4 numbers, or 3 number combination.

So if I entered 1, 2 ,3 ,4 ,5 ,6 for example, first the script would check if this 6 number combination has ever been draw, if so display the draw details (as above). But if the 6 numbers do not match, I would like to check the 6 numbers match any 5 number combination in any previous draws and then display the matching record(s), and so on for 4 number combination, 3 number combination - and display the matching records draw results.

My SQL Express table structure as follows (using SQL Express 2008):

Draw Number | day | month | year | Ball 1 | Ball 2 | Ball 3 | Ball 4 | Ball 5 | Ball 6 | Bonus Ball

As I mentioned above, my SQL are fairly basic, and was hoping someone point me in the right direction on which command/operators/statement would be best way to go for checking if any 5 number, 4 number, 3 number, combinations exist in any of the previous draws for the given input. I not asking for the full solution, but whether its possible to achieve this, and what would be the best approach.

I hope this make sense, here is an example:

Input values: 1,2,3,4,5,6

Previous draws:

Draw 1: 1, 2, 3, 4, 5, 6
Draw 2: 1, 2, 3, 4, 5, 41
Draw 3: 1, 2, 3, 4, 40, 41
Draw 4: 1, 2, 3, 30, 40, 41
Draw 5: 1, 10, 20, 30, 40, 41

Result:
Matched all six numbers in: Draw 1
Matched 5 numbers in: Draw 2
Matched 4 numbers in: Draw 3
Matched 3 numbers in: Draw 4

Thank you for reading, James

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2013-12-08 : 13:17:51

Not sure understand requirement correctly, but just run this to see if it works for you.


declare @temp table(id int, d1 int, d2 int, d3 int, d4 int, d5 int, d6 int);
insert into @temp values(1, 1, 2, 3, 4, 5, 6),
(2, 1, 2, 3, 4, 5, 41), (3, 1, 2, 3, 4, 40, 41 ),
(4, 1, 2, 3, 30, 40, 41),(5, 1, 10, 20, 30, 40, 41);
select * from @temp;

declare @input varchar(50);
set @input = '1,2,3,4,5,6';

;with cte as(
select t.*, @input input,
c = case when charindex(','+convert(varchar(10),d)+',', ','+@input+',') > 0 then 1 else 0 end
from @temp t
cross apply
(
select 1 as id , t.d1 as d union all
select 2 as id , t.d2 union all
select 3 as id , t.d3 union all
select 4 as id , t.d4 union all
select 5 as id , t.d5 union all
select 6 as id , t.d6
)tt)
select id, --d1,d2,d3,d4,d5,d6,input, 
returnMessage = 'Matched '+convert(varchar(10),sum(c))+' numbers in: Draw '+convert(varchar(10),id)
from cte group by id,d1,d2,d3,d4,d5,d6,input;

Result:

id d1 d2 d3 d4 d5 d6
1 1 2 3 4 5 6
2 1 2 3 4 5 41
3 1 2 3 4 40 41
4 1 2 3 30 40 41
5 1 10 20 30 40 41

id returnMessage
1 Matched 6 numbers in: Draw 1
2 Matched 5 numbers in: Draw 2
3 Matched 4 numbers in: Draw 3
4 Matched 3 numbers in: Draw 4
5 Matched 1 numbers in: Draw 5

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-09 : 16:15:56

[code]

DECLARE @draws TABLE (
[Draw Number] int PRIMARY KEY,
[day] tinyint,
[month] tinyint,
[year] smallint,
[Ball 1] smallint,
[Ball 2] smallint,
[Ball 3] smallint,
[Ball 4] smallint,
[Ball 5] smallint,
[Ball 6] smallint,
[Bonus Ball] smallint
)
INSERT INTO @draws ( [Draw Number], [Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6], [Bonus Ball] )
SELECT 1, 1, 2, 3, 4, 5, 6, 7 UNION ALL
SELECT 2, 1, 2, 3, 4, 5, 41, 9 UNION ALL
SELECT 3, 1, 2, 3, 4, 40, 41, 11 UNION ALL
SELECT 4, 1, 2, 3, 30, 40, 41, 13 UNION ALL
SELECT 5, 1, 10, 20, 30, 40, 41, 15
SELECT * FROM @draws

DECLARE @input1 int
DECLARE @input2 int
DECLARE @input3 int
DECLARE @input4 int
DECLARE @input5 int
DECLARE @input6 int

SET @input1 = 1
SET @input2 = 2
SET @input3 = 3
SET @input4 = 4
SET @input5 = 5
SET @input6 = 6

SELECT
@input1 AS input1,
@input2 AS input2,
@input3 AS input3,
@input4 AS input4,
@input5 AS input5,
@input6 AS input6,
standard_matches,
bonus_match
FROM @draws
CROSS APPLY (
SELECT
CASE WHEN @input1 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input2 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input3 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input4 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input5 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input6 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END AS standard_matches,
CASE WHEN [Bonus Ball] IN (@input1, @input2, @input3, @input4, @input5, @input6) THEN 1 ELSE 0 END AS bonus_match
) AS ca1
WHERE
standard_matches >= 3
--ORDER BY standard_matches DESC, bonus_match DESC

[/code]

jamez100
Starting Member

2 Posts

Posted - 2013-12-10 : 09:12:12

Hi Scott,

Many thanks for such a concise response, very much appreciated.

I have run the query in SQL studio and this is exactly what I was looking.

I have dropped the sample temp table created at the start, and now set the query to run against my data table (SELECT * FROM dbo.AllResultsTo091113$)

USE new1

SELECT * FROM dbo.AllResultsTo091113$

DECLARE @input1 int
DECLARE @input2 int
DECLARE @input3 int
DECLARE @input4 int
DECLARE @input5 int
DECLARE @input6 int

SET @input1 = 1
SET @input2 = 2
SET @input3 = 6
SET @input4 = 29
SET @input5 = 32
SET @input6 = 47

SELECT
@input1 AS input1,
@input2 AS input2,
@input3 AS input3,
@input4 AS input4,
@input5 AS input5,
@input6 AS input6,
standard_matches,
bonus_match

FROM dbo.AllResultsTo091113$

CROSS APPLY (
SELECT
CASE WHEN @input1 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input2 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input3 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input4 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input5 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END +
CASE WHEN @input6 IN ([Ball 1], [Ball 2], [Ball 3], [Ball 4], [Ball 5], [Ball 6]) THEN 1 ELSE 0 END AS standard_matches,
CASE WHEN [Bonus Ball] IN (@input1, @input2, @input3, @input4, @input5, @input6) THEN 1 ELSE 0 END AS bonus_match
) AS ca1
WHERE
standard_matches >= 3
--ORDER BY standard_matches DESC, bonus_match DESC

Regards, James

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-10 : 11:12:51

Relational Division?

-- Prepare environment
DECLARE	@Stage TABLE
	(
		Draw INT NOT NULL,
		Ball1 TINYINT NOT NULL,
		Ball2 TINYINT NOT NULL,
		Ball3 TINYINT NOT NULL,
		Ball4 TINYINT NOT NULL,
		Ball5 TINYINT NOT NULL,
		Ball6 TINYINT NOT NULL,
		Bonus TINYINT NOT NULL
	);

INSERT	@Stage
	(
		Draw,
		Ball1,
		Ball2,
		Ball3,
		Ball4,
		Ball5,
		Ball6,
		Bonus
	)
VALUES	(1, 1,  2,  3,  4,  5,  6, 19),
	(2, 1,  2,  3,  4,  5, 41, 18),
	(3, 1,  2,  3,  4, 40, 41, 17),
	(4, 1,  2,  3, 30, 40, 41, 16),
	(5, 1, 10, 20, 30, 40, 41, 15);

-- Prepare solution
DECLARE	@Ball1 INT = 1,
	@Ball2 INT = 2,
	@Ball3 INT = 3,
	@Ball4 INT = 4,
	@Ball5 INT = 5,
	@Ball6 INT = 6,
	@Bonus INT = 17;

-- SwePeso
SELECT		u.Draw,
		SUM(CASE WHEN u.theBall LIKE 'Ball[1-6]' AND u.Ball IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 ELSE 0 END) AS MatchingBalls,
		SUM(CASE WHEN u.theBall = 'Bonus' AND u.Ball = @Bonus THEN 1 ELSE 0 END) AS BonusBalls
FROM		@Stage AS s
UNPIVOT		(
			Ball
			FOR theBall IN (s.Ball1, s.Ball2, s.Ball3, s.Ball4, s.Ball5, s.Ball6, s.Bonus)
		) AS u
WHERE		u.Ball IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6, @Bonus)
GROUP BY	u.Draw;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources