Create view of winning lottery numbers

4 Aug 2006 4:48 PM

sean-usenet

Hello everyone, I hope someone can help me out with this.

I have a table the contains past winning number for multiple lottery
games. Following is the format of the table:

DrawDate,GameName,SetNum,WhichNum,WinNum
2006-07-31,2by2,0,0,26
2006-07-31,2by2,0,1,15
2006-07-31,2by2,1,0,5
2006-07-31,2by2,1,1,8
2006-08-01,2by2,0,0,15
2006-08-01,2by2,0,1,21
2006-08-01,2by2,1,0,13
2006-08-01,2by2,1,1,8
2006-08-02,2by2,0,0,15
2006-08-02,2by2,0,1,21
2006-08-02,2by2,1,0,3
2006-08-02,2by2,1,1,4
2006-08-02,Powerball,0,0,24
2006-08-02,Powerball,0,1,19
2006-08-02,Powerball,0,2,10
2006-08-02,Powerball,0,3,16
2006-08-02,Powerball,0,4,13
2006-08-02,Powerball,1,0,15
2006-08-02,Powerball,2,0,4
2006-08-03,2by2,0,0,20
2006-08-03,2by2,0,1,9
2006-08-03,2by2,1,0,16
2006-08-03,2by2,1,1,18
ect...

SetNum is which ball set, for example, Powerball has 3 ball sets (white
balls, Powerballs, and the Powerplay numbers). WhichNum is the order
the ball was drawn in, for example, the 1st ball drawn for that ballset
would have a WhichNum of 0, the 2nd ball picked for that ball set would
have a which num of 1, ect. WinNum is simple the winning number
picked.

For example for the 08/02/2006 Powerball drawing, the winning numbers
were as follows (in the order drawn):

White Balls: 24 19 10 16 13
Powerball: 15
Powerplay: 4

For the 7/31/2006 2by2 drawing, the winning numbers were as follows (in
the order drawn):

White Balls: 26 15
Red Balls: 05 08

I would like to create a view such as the following:

DrawDate,GameName,winnum1,winnum2,winnum3,winnum4,winnum5,winnum6,winnum7
2006-07-31,2by2,15,26,05,08,null,null,null
2006-08-02,Powerball,10,13,16,19,24,15,4
ect...

Notice how each drawing is now on 1 line, and the numbers are now
sorted in ascending order, instead of order drawn.

We are running MS SQL 2000

I just can figure out how to write a query to create such a view. Any
help would be VERY appreciated!

Thanks
Sean

4 Aug 2006 6:12 PM

Anith Sen

See if this link helps:
http://support.microsoft.com/kb/175574/en-us

--
Anith

4 Aug 2006 6:22 PM

sean-usenet

Thanks for the replay Anith. I also saw that article and I was able to
create the view, but the numbers are still in the order drawn instead
of ascending order. I can't find a way to have the number in ascending
order.

Thanks
Sean

Anith Sen wrote:
Show quote

> See if this link helps:
> http://support.microsoft.com/kb/175574/en-us
>
> --
> Anith

4 Aug 2006 6:27 PM

Anith Sen

Can you please post your table DDLs, code you are using & the expected
results? For details refer to: www.aspfaq.com/5006

--
Anith

4 Aug 2006 7:19 PM

sean-usenet

Sure

CREATE TABLE [WinNums] (
    [DrawDate] [datetime] NOT NULL ,
    [GameName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [SetNum] [tinyint] NOT NULL ,
    [WhichNum] [tinyint] NOT NULL ,
    [WinNum] [tinyint] NOT NULL
) ON [PRIMARY]
GO

Please see my original post for the expected results of the view i want
to create.

Thanks
Sean

Anith Sen wrote:
Show quote

> Can you please post your table DDLs, code you are using & the expected
> results? For details refer to: www.aspfaq.com/5006
>
> --
> Anith

4 Aug 2006 8:08 PM

Anith Sen

The idea is to have a ranking column that determines the order of winning
numbers. Here is an attempt to re-write the query:

SELECT DrawDate, GameName,
       MAX( CASE seq WHEN 1 THEN WinNum END ) AS "winnum1",
       MAX( CASE seq WHEN 2 THEN WinNum END ) AS "winnum2",
       MAX( CASE seq WHEN 3 THEN WinNum END ) AS "winnum3",
       MAX( CASE seq WHEN 4 THEN WinNum END ) AS "winnum4",
       MAX( CASE seq WHEN 5 THEN WinNum END ) AS "winnum5",
       MAX( CASE seq WHEN 6 THEN WinNum END ) AS "winnum6",
       MAX( CASE seq WHEN 7 THEN WinNum END ) AS "winnum7"
FROM ( SELECT w1.DrawDate, w1.GameName,
                w1.SetNum, w1.WhichNum, w1.WinNum,
               ( SELECT COUNT(*) FROM WinNums w2
                  WHERE w2.DrawDate = w1.DrawDate
                    AND w2.GameName = w1.GameName
                    AND w2.WinNum <= w1.WinNum )
           FROM WinNums w1
       ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
GROUP BY DrawDate, GameName ;

Here seq is the alias for the expression to get the ranking value mentioned
above. Note that you change/adjust the correlation in the subquery inside
the derived table to get the data displayed in any grouping level you want.

--
Anith

7 Aug 2006 3:22 PM

sean-usenet

Very nice work. I was able to adjust it to take into account SetNum,
so the winning numbers in each set were ordered. The following works
for Powerball:

SELECT DrawDate, GameName,
       MAX( CASE WHEN seq = 1 AND SetNum= 0 THEN WinNum END ) AS
"winnum1",
       MAX( CASE WHEN seq = 2 AND SetNum= 0 THEN WinNum END ) AS
"winnum2",
       MAX( CASE WHEN seq = 3 AND SetNum= 0 THEN WinNum END ) AS
"winnum3",
       MAX( CASE WHEN seq = 4 AND SetNum= 0 THEN WinNum END ) AS
"winnum4",
       MAX( CASE WHEN seq = 5 AND SetNum= 0 THEN WinNum END ) AS
"winnum5",
       MAX( CASE WHEN seq = 1 AND SetNum= 1 THEN WinNum END ) AS
"winnum6",
       MAX( CASE WHEN seq = 1 AND SetNum= 2 THEN WinNum END ) AS
"winnum7"
FROM ( SELECT w1.DrawDate, w1.GameName,
                w1.SetNum, w1.WhichNum, w1.WinNum,
               ( SELECT COUNT(*) FROM WinNums w2
                  WHERE w2.DrawDate = w1.DrawDate
                    AND w2.GameName = w1.GameName
                    AND w2.SetNum   = w1.SetNum
                    AND w2.WinNum <= w1.WinNum )
           FROM WinNums w1
       ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
where GameName='Powerball'
GROUP BY DrawDate, GameName

The query returns the following for 8/2/2006:

8/2/2006, Powerball, 10, 13, 16, 19, 24, 15, 4

Which is perfect for Powerball, the white balls are ordered and the
Powerball (15) and Powerplay (4) appear in the 6th and 7th positions.
Although, the query doesn't work for the other games in the table. Can
you think of a way to make the query take into account SetNum so the
winning numbers are ordered by ball set for each game's drawing,
without making the query game specific?

Thanks!
Sean

Anith Sen wrote:
Show quote

> The idea is to have a ranking column that determines the order of winning
> numbers. Here is an attempt to re-write the query:
>
> SELECT DrawDate, GameName,
>        MAX( CASE seq WHEN 1 THEN WinNum END ) AS "winnum1",
>        MAX( CASE seq WHEN 2 THEN WinNum END ) AS "winnum2",
>        MAX( CASE seq WHEN 3 THEN WinNum END ) AS "winnum3",
>        MAX( CASE seq WHEN 4 THEN WinNum END ) AS "winnum4",
>        MAX( CASE seq WHEN 5 THEN WinNum END ) AS "winnum5",
>        MAX( CASE seq WHEN 6 THEN WinNum END ) AS "winnum6",
>        MAX( CASE seq WHEN 7 THEN WinNum END ) AS "winnum7"
>   FROM ( SELECT w1.DrawDate, w1.GameName,
>                 w1.SetNum, w1.WhichNum, w1.WinNum,
>                ( SELECT COUNT(*) FROM WinNums w2
>                   WHERE w2.DrawDate = w1.DrawDate
>                     AND w2.GameName = w1.GameName
>                     AND w2.WinNum <= w1.WinNum )
>            FROM WinNums w1
>        ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
> GROUP BY DrawDate, GameName ;
>
> Here seq is the alias for the expression to get the ranking value mentioned
> above. Note that you change/adjust the correlation in the subquery inside
> the derived table to get the data displayed in any grouping level you want.
>
> --
> Anith

7 Aug 2006 3:30 PM

sean-usenet

I came up with this, what do you think?

SELECT DrawDate, GameName,
       MAX( CASE WHEN seq = 1 THEN WinNum END ) AS "winnum1",
       MAX( CASE WHEN seq = 2 THEN WinNum END ) AS "winnum2",
       MAX( CASE WHEN seq = 3 THEN WinNum END ) AS "winnum3",
       MAX( CASE WHEN seq = 4 THEN WinNum END ) AS "winnum4",
       MAX( CASE WHEN seq = 5 THEN WinNum END ) AS "winnum5",
       MAX( CASE WHEN seq = 6 THEN WinNum END ) AS "winnum6",
       MAX( CASE WHEN seq = 7 THEN WinNum END ) AS "winnum7"
FROM ( SELECT w1.DrawDate, w1.GameName,
                w1.SetNum, w1.WhichNum, w1.WinNum,
               ( SELECT COUNT(*) + (SELECT COUNT(*) FROM WinNums w3
WHERE w3.DrawDate = w1.DrawDate AND w3.GameName = w1.GameName AND
w3.SetNum < w1.SetNum)
        FROM WinNums w2
                  WHERE w2.DrawDate = w1.DrawDate
                    AND w2.GameName = w1.GameName
                    AND w2.SetNum   = w1.SetNum
                    AND w2.WinNum <= w1.WinNum )
           FROM WinNums w1
       ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
GROUP BY DrawDate, GameName

Thanks again for all your help
Sean
sean-usenet wrote:
Show quote

> Very nice work. I was able to adjust it to take into account SetNum,
> so the winning numbers in each set were ordered. The following works
> for Powerball:
>
> SELECT DrawDate, GameName,
>        MAX( CASE WHEN seq = 1 AND SetNum= 0 THEN WinNum END ) AS
> "winnum1",
>        MAX( CASE WHEN seq = 2 AND SetNum= 0 THEN WinNum END ) AS
> "winnum2",
>        MAX( CASE WHEN seq = 3 AND SetNum= 0 THEN WinNum END ) AS
> "winnum3",
>        MAX( CASE WHEN seq = 4 AND SetNum= 0 THEN WinNum END ) AS
> "winnum4",
>        MAX( CASE WHEN seq = 5 AND SetNum= 0 THEN WinNum END ) AS
> "winnum5",
>        MAX( CASE WHEN seq = 1 AND SetNum= 1 THEN WinNum END ) AS
> "winnum6",
>        MAX( CASE WHEN seq = 1 AND SetNum= 2 THEN WinNum END ) AS
> "winnum7"
>   FROM ( SELECT w1.DrawDate, w1.GameName,
>                 w1.SetNum, w1.WhichNum, w1.WinNum,
>                ( SELECT COUNT(*) FROM WinNums w2
>                   WHERE w2.DrawDate = w1.DrawDate
>                     AND w2.GameName = w1.GameName
>                     AND w2.SetNum   = w1.SetNum
>                     AND w2.WinNum <= w1.WinNum )
>            FROM WinNums w1
>        ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
> where GameName='Powerball'
> GROUP BY DrawDate, GameName
>
>
> The query returns the following for 8/2/2006:
>
> 8/2/2006, Powerball, 10, 13, 16, 19, 24, 15, 4
>
> Which is perfect for Powerball, the white balls are ordered and the
> Powerball (15) and Powerplay (4) appear in the 6th and 7th positions.
> Although, the query doesn't work for the other games in the table. Can
> you think of a way to make the query take into account SetNum so the
> winning numbers are ordered by ball set for each game's drawing,
> without making the query game specific?
>
> Thanks!
> Sean
>
>
> Anith Sen wrote:
> > The idea is to have a ranking column that determines the order of winning
> > numbers. Here is an attempt to re-write the query:
> >
> > SELECT DrawDate, GameName,
> >        MAX( CASE seq WHEN 1 THEN WinNum END ) AS "winnum1",
> >        MAX( CASE seq WHEN 2 THEN WinNum END ) AS "winnum2",
> >        MAX( CASE seq WHEN 3 THEN WinNum END ) AS "winnum3",
> >        MAX( CASE seq WHEN 4 THEN WinNum END ) AS "winnum4",
> >        MAX( CASE seq WHEN 5 THEN WinNum END ) AS "winnum5",
> >        MAX( CASE seq WHEN 6 THEN WinNum END ) AS "winnum6",
> >        MAX( CASE seq WHEN 7 THEN WinNum END ) AS "winnum7"
> >   FROM ( SELECT w1.DrawDate, w1.GameName,
> >                 w1.SetNum, w1.WhichNum, w1.WinNum,
> >                ( SELECT COUNT(*) FROM WinNums w2
> >                   WHERE w2.DrawDate = w1.DrawDate
> >                     AND w2.GameName = w1.GameName
> >                     AND w2.WinNum <= w1.WinNum )
> >            FROM WinNums w1
> >        ) D ( DrawDate, GameName, SetNum, WhichNum, WinNum, Seq )
> > GROUP BY DrawDate, GameName ;
> >
> > Here seq is the alias for the expression to get the ranking value mentioned
> > above. Note that you change/adjust the correlation in the subquery inside
> > the derived table to get the data displayed in any grouping level you want.
> >
> > --
> > Anith