Procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure help

Author

Topic

Naveensrcl
Starting Member

8 Posts

Posted - 2012-05-12 : 13:52:57

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-12 : 15:54:58

The brute force approach to doing this would be as follows:

CREATE PROCEDURE check_example_result
	@v1 INT, @v2 INT, @v3 INT, @v4 INT, @v5 INT, @v6 INT
AS
SELECT * FROM Example 
WHERE
	CASE WHEN col1 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END +
	CASE WHEN col2 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END +
	CASE WHEN col3 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END +
	CASE WHEN col4 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END +
	CASE WHEN col5 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END +
	CASE WHEN col6 IN (@v1, @v2, @v3, @v4, @v5,@v6) THEN 1 ELSE 0 END
	>= 5;

It is not scalable, but if that is not a requirement, this is simple and it works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 20:53:35

[code]
CREATE PROCEDURE check_example_result
@v1 INT, @v2 INT, @v3 INT, @v4 INT, @v5 INT, @v6 INT
AS

SELECT [drawn date] INTO #temp
FROM
(
SELECT [drawn date] ,ColVal
FROM Example
UNPIVOT (ColVal FOR Col IN (Col1,Col2,Col3,Col4,Col5,Col6))u
)t
INNER JOIN
(SELECT @V1 AS Val
UNION ALL
SELECT @V2
UNION ALL
SELECT @V3
UNION ALL
SELECT @V4
UNION ALL
SELECT @V5
UNION ALL
SELECT @V6
)t1
ON t1.Val = t.ColVal
GROUP BY [drawn date]
HAVING COUNT(ColVal) >=5

SELECT e.*
FROM Example e
INNER JOIN #temp t
ON e.[drawn date] = t.[drawn date]
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Naveensrcl
Starting Member

8 Posts

Posted - 2012-05-13 : 02:05:02

Thank you very much sunitabeck and visakh.

Great :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:28:32

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources