March 21, 2013 at 4:36 pm
I've been struggling on this and was hoping to get some help here on this. (Looking to automate lottery results)
I'm looking to update the LOTTERY_MY_NUM values that match in the LOTTERY_WINNING_NUM based on a row (RID column) and not a the set. Any column can match any column it just needs to be within the same row(s). (25 possible combinations) When a match is found, I would like to UPDATE the NUMx column with a value in LOTTERY_MY_NUM.
LOTTERY_WINNING_NUM_RAW - data being received in EXCEL file, being ingested via SSIS into SQL raw table.
LOTTERY_WINNING_NUM - Cleaned up data from raw, parsed out into columns.
LOTTERY_MY_NUM - My lottery numbers I want to match on the winning numbers.
--DROP TABLE LOTTERY_WINNING_NUM_RAW
CREATE TABLE LOTTERY_WINNING_NUM_RAW(
DATE VARCHAR(10),
NUMBERS VARCHAR (20))
--DROP TABLE LOTTERY_WINNING_NUM
CREATE TABLE LOTTERY_WINNING_NUM (
DATE VARCHAR(10),
RID INT IDENTITY(1,1),
NUM1 VARCHAR(20),
NUM2 VARCHAR(20),
NUM3 VARCHAR(20),
NUM4 VARCHAR(20),
NUM5 VARCHAR(20))
--DROP TABLE LOTTERY_MY_NUM
CREATE TABLE LOTTERY_MY_NUM (
DATE VARCHAR(30),
RID INT IDENTITY(1,1),
NUM1 VARCHAR(20),
NUM2 VARCHAR(20),
NUM3 VARCHAR(20),
NUM4 VARCHAR(20),
NUM5 VARCHAR(20))
INSERT INTO LOTTERY_WINNING_NUM_RAW
SELECT '3 19 2013','15 16 23 26 32'
INSERT INTO LOTTERY_WINNING_NUM_RAW
SELECT '3 18 2013','09 22 30 38 40'
INSERT INTO LOTTERY_WINNING_NUM_RAW
SELECT '3 17 2013','27 28 32 37 39'
INSERT INTO LOTTERY_WINNING_NUM
SELECT
REPLACE(DATE, ' ', '/'),
SUBSTRING (NUMBERS, 1, 2) AS NUM1,
SUBSTRING (NUMBERS, 4, 2) AS NUM2,
SUBSTRING (NUMBERS, 7, 2) AS NUM3,
SUBSTRING (NUMBERS, 10, 2) AS NUM4,
SUBSTRING (NUMBERS, 13, 2) AS NUM5
FROM LOTTERY_WINNING_NUM_RAW
DROP TABLE LOTTERY_MY_NUM
CREATE TABLE LOTTERY_MY_NUM (
DATE VARCHAR(30),
RID INT IDENTITY(1,1),
NUM1 VARCHAR(20),
NUM2 VARCHAR(20),
NUM3 VARCHAR(20),
NUM4 VARCHAR(20),
NUM5 VARCHAR(20))
INSERT INTO LOTTERY_MY_NUM
SELECT
'03/18 - 03/22', '17', '20', '28', '39', '40'
INSERT INTO LOTTERY_MY_NUM
SELECT
'03/18 - 03/22', '03', '10', '16', '23', '40'
INSERT INTO LOTTERY_MY_NUM
SELECT
'03/18 - 03/22', '07', '21', '26', '31', '38'
INSERT INTO LOTTERY_MY_NUM
SELECT
'03/18 - 03/22', '02', '05', '27', '34', '38'
INSERT INTO LOTTERY_MY_NUM
SELECT
'03/18 - 03/22', '05', '18', '22', '31', '37'
SELECT * FROM LOTTERY_WINNING_NUM_RAW
SELECT * FROM LOTTERY_WINNING_NUM
SELECT * FROM LOTTERY_MY_NUM
March 22, 2013 at 7:38 am
I've been looking at this and is it a case of you want to match all entries against the draws they were entered for or is it to match for any draw that has occurred regardless of the time span?
I'm also not clear on the reason why you want to update the numbers that have been matched because by definition they are the same. Surely a better way is to look at the ones that have been matched and for the process to tell you where the matches are.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 22, 2013 at 8:42 am
I think I did not understand ur requirement properly
Is this what u want???
Update LOTTERY_MY_NUM set NUM1= LOTTERY_WINNING_NUM.NUM1,
NUM2= LOTTERY_WINNING_NUM.NUM2,
NUM3= LOTTERY_WINNING_NUM.NUM3,
NUM4= LOTTERY_WINNING_NUM.NUM4,
NUM5= LOTTERY_WINNING_NUM.NUM5
from LOTTERY_MY_NUM A JOIN LOTTERY_WINNING_NUM ON A.RID = LOTTERY_WINNING_NUM.RID
(3 row(s) affected)
--Pra:-):-)--------------------------------------------------------------------------------
March 22, 2013 at 9:42 am
The numbers need to match within the row and not within the set. I was hoping the identity would help with this but like I said, I'm struggling. For example, I have a lottery ticket with 5 rows. These 5 rows get inserted into LOTTERY_MY_NUM these need to be compared to each row in the LOTTERY_WINNING_NUM table. Each row can have 25 possible combinations. NUM1 on LOTTERY_MY_NUM needs to be checked on NUM1, NUM2, NUM3, NUM4, NUM5. This routine needs to be done for NUM2, NUM3, NUM4, NUM5. Then loop through the 4 remaining rows.
The reason for the update is, I want to CAST BcolorCode + NUMx. The number is not being replaced, just appending a string to existing value. This is the reason for the datatypes in the NUM fields being varchar and not int.
When I am sending out the email I will look for that string in the table. This will highlight the matching numbers.
SET @tableHTML = replace(@tableHTML, '>BcolorCode', ' bgcolor="yellow">')
Hope this makes sense.
March 22, 2013 at 9:54 am
SQLSeTTeR (3/22/2013)
The numbers need to match within the row and not within the set. I was hoping the identity would help with this but like I said, I'm struggling. For example, I have a lottery ticket with 5 rows. These 5 rows get inserted into LOTTERY_MY_NUM these need to be compared to each row in the LOTTERY_WINNING_NUM table. Each row can have 25 possible combinations. NUM1 on LOTTERY_MY_NUM needs to be checked on NUM1, NUM2, NUM3, NUM4, NUM5. This routine needs to be done for NUM2, NUM3, NUM4, NUM5. Then loop through the 4 remaining rows.The reason for the update is, I want to CAST BcolorCode + NUMx. The number is not being replaced, just appending a string to existing value. This is the reason for the datatypes in the NUM fields being varchar and not int.
When I am sending out the email I will look for that string in the table. This will highlight the matching numbers.
SET @tableHTML = replace(@tableHTML, '>BcolorCode', ' bgcolor="yellow">')
Hope this makes sense.
Ok, I sort of understand I came up with this it doesn't do an update but tesl you what the matches are and where they are
CREATE TABLE #Lottery_Winning_Num_Raw
(
LotteryDate VARCHAR (10),
NUMBERS VARCHAR (20)
)
CREATE TABLE #LotteryWinningNumbers
(
LotteryDate DATE NOT NULL PRIMARY KEY
, NUM1 TINYINT NOT NULL
, NUM2 TINYINT NOT NULL
, NUM3 TINYINT NOT NULL
, NUM4 TINYINT NOT NULL
, NUM5 TINYINT NOT NULL
)
CREATE TABLE #MyLotteryNumbers
(
LineId INT Identity(1,1) NOT FOR REPLICATION
, LotteryDateStart DATE NOT NULL
, LotteryDateEnd DATE NOT NULL
, NUM1 TINYINT NOT NULL
, NUM2 TINYINT NOT NULL
, NUM3 TINYINT NOT NULL
, NUM4 TINYINT NOT NULL
, NUM5 TINYINT NOT NULL
)
INSERT INTO #Lottery_Winning_Num_Raw
SELECT '3 19 2013','15 16 23 26 32'
INSERT INTO #Lottery_Winning_Num_Raw
SELECT '3 18 2013','09 22 30 38 40'
INSERT INTO #Lottery_Winning_Num_Raw
SELECT '3 17 2013','27 28 32 37 39'
INSERT INTO #LotteryWinningNumbers
SELECT
convert(DATE,REPLACE(LotteryDate, ' ', '/')),
SUBSTRING (NUMBERS, 1, 2) AS NUM1,
SUBSTRING (NUMBERS, 4, 2) AS NUM2,
SUBSTRING (NUMBERS, 7, 2) AS NUM3,
SUBSTRING (NUMBERS, 10, 2) AS NUM4,
SUBSTRING (NUMBERS, 13, 2) AS NUM5
FROM #Lottery_Winning_Num_Raw
INSERT INTO #MyLotteryNumbers
SELECT '03/18/2013' ,'03/22/2013', 17, 20, 28, 39, 40
INSERT INTO #MyLotteryNumbers
SELECT '03/18/2013', '03/22/2013',03, 10, 16, 23, 40
INSERT INTO #MyLotteryNumbers
SELECT '03/18/2013' ,'03/22/2013', 07, 21, 26, 31, 38
INSERT INTO #MyLotteryNumbers
SELECT '03/18/2013','03/22/2013', 02, 05, 27, 34, 38
INSERT INTO #MyLotteryNumbers
SELECT '03/18/2013','03/22/2013',05, 18, 22, 31, 37
Select * from #LotteryWinningNumbers
Select * from #MyLotteryNumbers
DECLARE @UpDateIns varchar(MAX)
;WITH Cte_Dates
AS (
SELECT '03/18/2013' aDate
UNION SELECT '03/19/2013'
UNION SELECT '03/20/2013'
UNION SELECT '03/21/2013'
UNION SELECT '03/22/2013'
),
Cte_MyEntries
AS (
Select Cte_Dates.aDate as EntryDate,LineId,MyIndex,Num
from #MyLotteryNumbers
JOIN Cte_Dates ON aDate Between LotteryDateStart and LotteryDateEnd
CROSS APPLY (VALUES (1,#MyLotteryNumbers.NUM1),(2,#MyLotteryNumbers.NUM2),(3,#MyLotteryNumbers.NUM3),(4,#MyLotteryNumbers.NUM4),(5,#MyLotteryNumbers.NUM5)) x (MyIndex,Num)
), Cte_LotteryNums
AS (
Select LotteryDate DrawDate,DrawNumber
FROM
#LotteryWinningNumbers WinNumbs
CROSS APPLY (VALUES (1,WinNumbs.NUM1),(2,WinNumbs.NUM2),(3,WinNumbs.NUM3),(4,WinNumbs.NUM4),(5,WinNumbs.NUM5)) y (NumIndex,DrawNumber)
)
, Cte_MatchedNums
AS (
SELECT Ent.EntryDate,DrawDate, DrawNumber,Ent.MyIndex Position, LineId
from Cte_MyEntries Ent
JOIN Cte_LotteryNums Draw on ent.EntryDate=Draw.DrawDate
Where
Draw.DrawNumber=ent.Num
)
, MatchedLines
AS (
SELECT DISTINCT
aDate
, Line.LineId LineNumber
,NUM1
,NUM2
,NUM3
,NUM4
,NUM5
, 'Draw '
+Convert(varchar,[Matched].DrawDate)+ ' Matched Number(s) : '
+ STUFF(
(Select ','+convert(Varchar,DrawNumber)
From Cte_MatchedNums
Where LineId=Line.LineId
AND DrawDate =Cte_Dates.aDate
FOR XML PATH('')),1,1,'') MatchDetails
, 'Number Positions : '
+ STUFF(
(Select ','+ convert(Varchar,Position)
From Cte_MatchedNums
Where LineId=Line.LineId
AND DrawDate =Cte_Dates.aDate
FOR XML PATH('')),1,1,'') MatchPosition
from Cte_MatchedNums [Matched]
JOIN #MyLotteryNumbers Line on Line.LineId=[Matched].LineId
JOIN Cte_Dates ON aDate Between LotteryDateStart and LotteryDateEnd
WHERE
[Matched].DrawDate=aDate
)
Select * from MatchedLines
Drop Table #LotteryWinningNumbers
Drop Table #Lottery_Winning_Num_Raw
Drop Table #MyLotteryNumbers
I'm sure it can be simplified, and made simpler I just haven't had that much time to work on it during the day.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 22, 2013 at 10:07 am
Thank you very much. Can you please explain to me the result table (MatchedLines) ?
March 22, 2013 at 10:09 am
Guess my question is, shouldn't there be 5 rows and not 7?
March 22, 2013 at 2:02 pm
Right there with Babe,
This is exactly what I needed. Very nice coding, I will learn from this. Thank you very, very much!
All the best,
SQLSeTTer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply