SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE HELP


UPDATE HELP

Author
Message
SQLSeTTeR
SQLSeTTeR
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 1395
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
prathibha_aviator
prathibha_aviator
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 271
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:-):-)--------------------------------------------------------------------------------
SQLSeTTeR
SQLSeTTeR
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 1395
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.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
SQLSeTTeR
SQLSeTTeR
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 1395
Thank you very much. Can you please explain to me the result table (MatchedLines) ?
SQLSeTTeR
SQLSeTTeR
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 1395
Guess my question is, shouldn't there be 5 rows and not 7?
SQLSeTTeR
SQLSeTTeR
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 1395
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search