Just for fun Lotto SQL
-
The data table looks like this
date num1 num2 num3 num4 num5 num6
1/1/06 1 45 32 21 15 48
SQL script 1 should be able to do the following
show the Top 3 number pairs
another words if the pair 45 and 15 shows up 98 times and 32 and 12 shows up 3 times
then I want to have 45 and 15 show as one of the top 3 number pairs
I know this is a difficult one
So that being said see if I can re-explain it here
I have say two dates
1/1/2006 and 1/2/2006
on the first date I have six numbers being
1,24,45,35,47,14
and the second date I have
24,35,9,15,46,17
Now I can if it is needed to re do the data structure to reflect it like this
date num
1/1/2006 1
1/1/2006 24
1/1/2006 45
1/1/2006 35
1/1/2006 47
1/1/2006 14
1/2/2006 24
1/2/2006 35
1/2/2006 9
1/2/2006 15
1/2/2006 46
1/2/2006 17
Now somehow I am not sure how but I need to take the six numbers from each date and compare them with every other date.
so another words from date one take 1 and 24 and see if that combo matches other dates then go to 1 and 45 and do the same and so on.
-
I don't have time to code it nor do I have a server to test it so I'll just give you the logic and then you should be able to go from there :
PS I'm assuming the date fields do not containt a time value (all set to midnight). If not, you'll have to strip the time part ( DATEADD(D, 0, DATEDIFF(D, 0, DateField)) )
Create a temp table with fields (Date, Number)
Insert into #t1 Select Date, N1 FROM Table
UNION ALL
Select Date, N2 FROM TABLE
UNION ALL
...
--UP the n5
Create a temp table with fields (Date, N1, N2)
Insert into #t2
Select t1.Date, T1.Number As N1, T2.Number AS N2
FROM #t1 t1 inner join #t1 t2 ON t1.Date = T2.Date and T1.Number < T2.Number
Final query :
Select TOP 3 WITH TIES Count(*) AS Total, N1, N2
FROM #t2
--Optional
--WHERE Date >= @Date1 AND Date < @Date2
GROUP BY N1, N2 ORDER BY Count(*) DESC
-
PS, it can be done without #temp tables or table variables if need be... but performance wise and code readability wise, I wouldn't recommend it
. -
where are you getting the data for the numbers that have been won in the past?
-
I know there is no way of actually predicting the lotto but I have been getting bugged by a couple people to put something together for them that is why I am doing this
-
I would definitely change your data model. It's a lot easier and quicker to concatenate values into a string than it is to unconcatenate (is that a word?) them.
Plus what if the lottery changes the number of numbers in each drawing (like my state did twice) or if there are different types of lottery games you want to track that have different numbers of numbers?
I would also include a field to indicate the pick number to accomodate lotteries that allow numbers to be repeated.
My state's lottery changed from 6 numbers to 5 numbers plus a "powerball" number. The "powerball" number could be any number even one that was already picked in one of the other positions. It wasn't a popular game, so it was changed back to 6 unique numbers.
And lastly, because date is very important in the lottery, I would store the date as an integer (i.e., 20061204 = Dec. 4, 2006 in yyyymmdd format aka ISO format) to optimize searches. This number format can be easily be found by converting the date to varchar with a style of 112. By formatting it as yyyymmdd, it ensures that the integers are evaluated in the same order as the dates would be.
MyLottoPicks: PickDate int, PickPosition int, PickNumber int
Declare @Date datetime, @iDate = int
Set @Date = getdate()
Set @iDate = Cast(Convert(varchar, @Date, 112) as Int)
Select LP.PickDate, LP.PickPosition, LP.PickNumber, PickStats.PickCount
From MyLottoPicks As LP with(nolock)
Inner Join (Select PickNumber, Count(PickNumber) As PickCount
From MyLottoPicks with(nolock)
Group By PickNumber) As PickStats On PickStats.PickNumber = LP.PickNumber
Where LP.PickDate = @iDate
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
--set joe celko angry rant mode on
Just a question here. Do you store you ints in varchars and your varchars in ntext.
If you really need to speed this query up that badly, why not use the smalldatetime format (4 bytes, same as int). But I mean it's only 52 rows / year, I don't see how this could really improve performance here. Especially not at the cost of losing the native datatype.
--set joe celko angry rant mode off
Back to my cheery self
. -
--Execute as joe celko; set angry rant mode on
Using smalldate would create a Y2K79 bug. I always assume that my scripts won't be completely obsolete by June 6, 2079.
--Revert;
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Convert to date time then... I can assume the computing power will be so great by then than a 5000 rows table won't take more than one 0.0000000001 MS to process.
-
I'll save a bookmark so that we can finish this discussion in ±70 years
. -
Actually that would be 72 years its basically 2007 now so it will be 72 years when 2k79 hits
just an FYI....
-
I'll be over 100 by then so hopefully I've managed to retire to a big ranch in TX and you can talk amongst yourselves.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
IIRC the Y2K bug was being talked about way before 2000. That's why I gave a 2 years notice... You know 'cause running an alter table command can take that much preparation
. -
The way things are moving along I think both coding and computing power will intensify so greatly by then that well
Hell what are we talking about again
LOL
-
Don't know.. I'll be long retired by then too
.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply