One of the benefits of storing the winning number in a table like tblWinDigits where the only essential columns are the Drawing, position and number is that it makes it really easy to look at winning results.
I use it like this on the page that I use to collect the numbers from the lotto web site. After it has harvested the number it automatically refreshes the results with whatever I played that drawing.
It also works for Lotto with the bonus number. It does not work for MM or PB, but could easily be modified to do so.
I use the following tables to store the numbers that I have picked.
The following query is used to build the results:
IF OBJECT_ID ('dbo.GAMES_FN_Results', N'IF') IS NOT NULL
DROP FUNCTION dbo.GAMES_FN_Results;
GO
Create Function dbo.GAMES_FN_Results(@StartDrwing int ,@EndDrwing int, @PerGameCost money)
returns Table
as
return
Select
Max(drwing) as LastDrwing, Max(dteDate) as LastDrwingDate ,PickID,GameCard , GameNumber , StartDrwing , EndDrwing, EndDrwing - Max(Drwing) as Remaining ,PickMethod as PickMethod , PickedNums
,Sum(Case Drwing when @EndDrwing then Hits else 0 end) as CurrDrwingHit,Sum(Case Drwing when @EndDrwing then Cash else 0 end) as CurrDrwingPrize
,Max(Case Drwing when @EndDrwing then MatchedBonus else 0 end) as CurrDrwingMatchedBonus
, Max(Hits) as MaxHit, Min(Hits) as MinHit , Sum(Cash) as TotalCash, Max(Cash) as MaxPrize, min(Cash) as MinPrize
, Sum(case cash when 0 then 0 else 1 end ) as WinCount, sum(MoneySpent ) as MoneySpent, Sum(Cash) - sum(MoneySpent ) as NetProfit
from
(Select
Drwing, dteDate , pickID, StartDrwing ,EndDrwing, GameCard , GameNumber , PickMethod , PickedNums , s.Hits , MatchedBonus , Cast(1 * @PerGameCost as money ) as MoneySpent
,case when bp.hits is null then p.Cash else bp.Cash end as Cash
from
(Select h.PickID , h.Drwing , h.StartDrwing , h.EndDrwing, GameCard , GameNumber , h.dteDate
, isnull( dt.Hits,0) as Hits, isnull( dt.Bonus,0) as Bonus, isnull( dt.MatchedBonus,0) as MatchedBonus
,PickMethod, PickedNums from
(Select g.PickID ,g.GameCard , g.GameNumber , StartDrwing , EndDrwing , w.Drwing , w.dteDate, PickMethod , PickedNums from vwGames as G
inner join tblWinNums as W on w.Drwing between g.StartDrwing and g.EndDrwing
where (w.Drwing between @StartDrwing and @EndDrwing and @StartDrwing <> @EndDrwing )
Or (@StartDrwing = @EndDrwing and w.Drwing <= @EndDrwing and @EndDrwing Between StartDrwing and EndDrwing )
) as H
left outer join
(Select g.PickID , wd.Drwing, Sum( case wd.Pos when 99 then 0 else 1 end ) as Hits
, Sum( case wd.Pos when 99 then 10 else 1 end ) as Bonus, Sum( case wd.Pos when 99 then 1 else 0 end ) as MatchedBonus
from tblGames as G
inner join tblGameDigits as D on g.PickID = d.PickID
inner join tblWinDigits as WD on d.N0 = wd.N0 and wd.Drwing between g.StartDrwing and g.EndDrwing
where (wd.Drwing between @StartDrwing and @EndDrwing and @StartDrwing <> @EndDrwing )
Or (@StartDrwing = @EndDrwing and wd.Drwing <= @EndDrwing and @EndDrwing Between StartDrwing and EndDrwing )
group by g.PickID , wd.Drwing ) As DT on H.PickID = dt.PickID and h.Drwing = dt.Drwing ) as S
inner join tblPrizes as P on s.Hits = p.Hits
Left outer join tblPrizes as BP on s.Bonus = bp.Hits ) as PM
group by pickid, StartDrwing , EndDrwing ,PickMethod , PickedNums ,GameCard , GameNumber