SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Finding Matching Numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamez100
Starting Member

United Kingdom
2 Posts

Posted - 12/07/2013 :  17:23:36  Show Profile  Reply with Quote
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

USA
272 Posts

Posted - 12/08/2013 :  13:17:51  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
409 Posts

Posted - 12/09/2013 :  16:15:56  Show Profile  Reply with Quote


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

Go to Top of Page

jamez100
Starting Member

United Kingdom
2 Posts

Posted - 12/10/2013 :  09:12:12  Show Profile  Reply with Quote
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

Edited by - jamez100 on 12/10/2013 09:31:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/10/2013 :  11:12:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000