May 12, 2012 at 11:55 am
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
thanks,
May 12, 2012 at 9:14 pm
I've not checked it for performance but it does avoid a full cross join and it does work.
WITH
cteUnPivot AS
(
SELECT ex.[Drawn Date], ca.Number
FROM dbo.Example ex
CROSS APPLY
(
SELECT Col1 UNION ALL
SELECT Col2 UNION ALL
SELECT Col3 UNION ALL
SELECT Col4 UNION ALL
SELECT Col5 UNION ALL
SELECT Col6
) ca (Number)
),
cteFindDates AS
(
SELECT [Drawn Date]
FROM cteUnPivot
WHERE Number IN (17,4,23,42,12,100)
GROUP BY [Drawn Date]
HAVING COUNT(*) >=5
)
SELECT ex.*
FROM dbo.Example ex
INNER JOIN cteFindDates fd
ON ex.[Drawn Date] = fd.[Drawn Date]
;
If you decide to pass the numbers in as a single parameter, you'll need to split them into their own table to do the "IN" join on.
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply