Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE HELP Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 4:36 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 219, Visits: 1,157
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
Post #1434071
Posted Friday, March 22, 2013 7:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:09 AM
Points: 878, Visits: 2,395
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
Post #1434269
Posted Friday, March 22, 2013 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, 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--------------------------------------------------------------------------------
Post #1434309
Posted Friday, March 22, 2013 9:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 219, Visits: 1,157
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.
Post #1434340
Posted Friday, March 22, 2013 9:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:09 AM
Points: 878, Visits: 2,395
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
Post #1434348
Posted Friday, March 22, 2013 10:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 219, Visits: 1,157
Thank you very much. Can you please explain to me the result table (MatchedLines) ?
Post #1434362
Posted Friday, March 22, 2013 10:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 219, Visits: 1,157
Guess my question is, shouldn't there be 5 rows and not 7?
Post #1434369
Posted Friday, March 22, 2013 2:02 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 219, Visits: 1,157
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
Post #1434509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse