Results 1 to 2 of 2

Thread: Need Procedure

  1. #1
    Join Date
    May 2012
    Posts
    3

    Thumbs down Unanswered: Need Procedure

    Hi, below is the details,

    create table Example
    (
    Col1 int ,
    Col2 int ,
    Col3 int ,
    col4 int ,
    col5 int ,
    col6 int ,
    [drawn date] datetime,
    Winners int
    )
    go

    insert into Example
    select 39, 32, 34, 3, 8, 30,'2012-01-02', 0 union all
    select 12, 4, 17, 22, 23, 42,'2012-02-05', 2 union all
    select 32, 28, 39, 26, 37, 42,'2012-05-09', 0

    select * from Example

    i want a sql that given 6 numbers it would give the result for those matching at least 5 numbers and the dates.
    so when I execute stored proc check_example_result(17,4,23,42,12,100) it will output record#2

    it will basically check a given number against the data and if at least 5 out of the 6 given numbers matches it will display that record.

    please help me

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume the 6 given numbers are @n1 through @n6
    Code:
    SELECT col1 , col2 , col3 , col4 , col5 , col6
         , `drawn date`
         , winners
      FROM example
     WHERE CASE WHEN col1 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )     
                THEN 1 ELSE 0 END 
         + CASE WHEN col2 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )
                THEN 1 ELSE 0 END 
         + CASE WHEN col3 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )
                THEN 1 ELSE 0 END 
         + CASE WHEN col4 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )
                THEN 1 ELSE 0 END 
         + CASE WHEN col5 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )
                THEN 1 ELSE 0 END 
         + CASE WHEN col6 in ( @n1 , @n2 , @n3 , @n4 , @n5 , @n6 )
                THEN 1 ELSE 0 END 
        >= 5
    note the backticks around `drawn date`

    this is the mysql forum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •