SQL Query Again - Complex

Question

SQL Query Again - Complex

Viewing 15 posts - 31 through 45 (of 78 total)

You must be logged in to reply to this topic. Login to reply

raj-248044 SSC Eights! Points: 838 More actions · Answer 1

I changed the column for brevity...So its easier for you guys..Since my Column names are long...eg. BET_NUMBER_1. hate to write it that long, so i changed it to short form..

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

hmm! I just ran Nicolas' code against a test table and it worked fine...do you get any error messages at all ?! I inserted all the data exactly as you had posted...?!?!

**ASCII stupid question, get a stupid ANSI !!!**

philcart SSC-Forever Points: 47794 More actions · Answer 3

Haven't had much of a look at this, but try this on for size. Copy and paste the entire code into Query Analyzer.

SET NOCOUNT ON

CREATE TABLE #BETS (
 [BetID] [int] NOT NULL ,
 [DrawID] [int] NOT NULL ,
 [UserID] [int] NOT NULL ,
 [Num1] [int] NOT NULL ,
 [Num2] [int] NOT NULL ,
 [Num3] [int] NOT NULL ,
 [Num4] [int] NOT NULL ,
 [Num5] [int] NOT NULL ,
 [Num6] [int] NOT NULL ,
 [Num7] [int] NOT NULL ,
 [Num8] [int] NOT NULL
)

CREATE TABLE #MatchingBets (
    BetID int NOT NULL,
    Matches int NOT NULL
)

INSERT INTO #Bets VALUES (1,1,1001,1,2,4,7,12,19,21,26)
INSERT INTO #Bets VALUES (2,1,1001,4,2,6,7,17,29,33,45)
INSERT INTO #Bets VALUES (3,1,1001,1,2,4,7,10,19,23,35)
INSERT INTO #Bets VALUES (4,1,1001,5,7,21,22,32,37,40,43)

PRINT 'Stored Bets data'
PRINT REPLICATE('-', 20)
SELECT * FROM #Bets

DECLARE @Win1 int, @Win2 int, @Win3 int, @Win4 int, @Win5 int

SELECT @Win1 = 1, @Win2 = 4, @Win3 = 7, @Win4 = 19, @Win5 = 33

INSERT INTO #MatchingBets
SELECT BetID, 0 as MatchingBets
FROM #Bets

UPDATE #MatchingBets
SET Matches = 
    (CASE WHEN NUM1 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM2 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM3 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM4 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM5 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM6 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM7 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
    + (CASE WHEN NUM8 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)
FROM #Bets
    INNER JOIN #MatchingBets
    ON #Bets.BETID = #MatchingBets.BETID

PRINT 'Matched Bets totals'
PRINT REPLICATE('-', 20)
SELECT * FROM #MatchingBets

PRINT 'Details for Matched Bets'
PRINT REPLICATE('-', 20)
SELECT #Bets.BetID,DrawID,UserID,Num1,Num2,Num3,Num4
    ,Num5,Num6,Num7,Num8,#MatchingBets.Matches 
FROM #Bets
    INNER JOIN #MatchingBets
    ON #Bets.BETID = #MatchingBets.BETID

DROP TABLE #Bets
DROP TABLE #MatchingBets

Now, presuming that the #Bets table in my example code is a permanent table in the posters system. It should be relatively easy for some of you guys that have more time to provide a solution that doesn't involve the second table. Also, I'm all for one of the previous posts about changing the table structure. You shouldn't have Num1, Num2, Num3, etc... as seperate fields in the table.

--------------------
Colt 45 - the original point and click interface

raj-248044 SSC Eights! Points: 838 More actions · Answer 4

BET_ID DRAW_ID BET_NUMBER_1 BET_NUMBER_2 BET_NUMBER_3 BET_NUMBER_4 BET_NUMBER_5 BET_NUMBER_6 BET_NUMBER_7 BET_NUMBER_8

----------- ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------

6 1 1 6 7 8 3 2 12 16

7 1 1 2 3 4 6 8 17 25

8 1 1 2 4 8 9 6 24 34

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

here's the wholet thing from start to finish - apologies for the mishmash of upper and lower case...is late..

create table bets

(betid int,

drawid int,

userid int,

num1 int,

num2 int,

num3 int,

num4 int,

num5 int,

num6 int,

num7 int,

num8 int)

insert into bets values(1, 1, 1001, 1, 2, 4, 7, 12, 19, 21, 26)

insert into bets values(2, 1, 1001, 4, 2, 6, 7, 17, 29, 33, 45)

insert into bets values(3, 1, 1001, 1, 2, 4, 7, 10, 19, 23, 35)

insert into bets values(4, 1, 1001, 5, 7, 21, 22, 32, 37, 40, 43)

declare @Winner_1 int

declare @Winner_2 int

declare @Winner_3 int

declare @Winner_4 int

declare @Winner_5 int

SET @Winner_1 = 1

SET @Winner_2 = 2

SET @Winner_3 = 3

SET @Winner_4 = 4

SET @Winner_5 = 5

SELECT BetID, 0 as MatchingBets

INTO #TempBets

FROM BETS

UPDATE #TempBets

SET MatchingBets = ( CASE WHEN num1 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END )

+

( CASE WHEN num2 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END )

+

( CASE WHEN num3 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

+

( CASE WHEN num4 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

+

( CASE WHEN num5 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

FROM BETS

WHERE #TempBets.BETID = BETS.BETID

select * from #TempBets

drop table #TempBets

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

oops - colt 45 is drawn and at the ready!

**ASCII stupid question, get a stupid ANSI !!!**

raj-248044 SSC Eights! Points: 838 More actions · Answer 7

DECLARE @Win1 int, @Win2 int, @Win3 int, @Win4 int, @Win5 int

SELECT @Win1 = 1, @Win2 = 4, @Win3 = 7, @Win4 = 19, @Win5 = 33

INSERT INTO #MatchingBets

SELECT BetID, 0 as MatchingBets

FROM #Bets

UPDATE #MatchingBets

SET Matches =

(CASE WHEN NUM1 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM2 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM3 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM4 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM5 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM6 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM7 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM8 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

FROM #Bets

INNER JOIN #MatchingBets

ON #Bets.BETID = #MatchingBets.BETID

I Used the codes given and it WORKED!!! Thanks...

But I need some adjustments, since the MatchingBets has only BETID and MatchingBets. I would like to Add in 2 More Columns too, which are my USERID and BETTYPE, while perfroming this query.

Also, as stated, That there are 5winning Numbers. I have actually added a Mystery Number. So anyone whom has this mystery number will win extra bonus.

So, some slight modification.

Matches = 5 Winning Numbers, Mystery = 1Mystery Number

So i need to query for the 5 numbers and 1 Mystery Number and populate the Matches and Mystery Column. I also need to capture the USERID and BETTYPE too inside my MatchingBets Table.

Sorry If im buggy, but Im learning SQL slowly, have to take step by step, in understanding the commands etc.

I hope you do not misunderstand me.

raj-248044 SSC Eights! Points: 838 More actions · Answer 8

Anyone...Since i got to get this done asap...Thanks in advance..

David Burrows SSC Guru Points: 65141 More actions · Answer 9

SELECT BetID,USERID,BETTYPE,0 as Matches,0 as Mystery

INTO #MatchingBets

FROM #Bets

UPDATE #MatchingBets

SET Matches =

(CASE WHEN NUM1 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM2 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM3 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM4 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM5 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM6 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM7 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)

+ (CASE WHEN NUM8 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END),

Mystery = (CASE WHEN @Mystery IN (NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,NUM7,NUM8) THEN 1 ELSE 0 END)

FROM #Bets

INNER JOIN #MatchingBets

ON #Bets.BETID = #MatchingBets.BETID

Far away is close at hand in the images of elsewhere.
Anon.

raj-248044 SSC Eights! Points: 838 More actions · Answer 10

Im getting this error.

Server: Msg 2714, Level 16, State 6, Line 14

There is already an object named 'MatchingBets' in the database.

David Burrows SSC Guru Points: 65141 More actions · Answer 11

Are you using a permanent or temp table?

If you are using a temp table you will get this error in QA if you do not drop the temp table when processing has finished.

Add this line to the end of the whole query

DROP TABLE #MatchingBets

and execute that line once and then run the whole query again

Far away is close at hand in the images of elsewhere.
Anon.

raj-248044 SSC Eights! Points: 838 More actions · Answer 12

I created a new table just for this..Its permanenet

David Burrows SSC Guru Points: 65141 More actions · Answer 13

In that case change the code to

DELETE FROM TABLE MatchingBets

INSERT INTO INTO MatchingBets

(BetID,USERID,BETTYPE,Matches,Mystery)

SELECT BetID,USERID,BETTYPE,0,0

FROM Bets

and make sure the table structure contains the following columns in the correct order BetID,USERID,BETTYPE,Matches,Mystery

Far away is close at hand in the images of elsewhere.
Anon.

raj-248044 SSC Eights! Points: 838 More actions · Answer 14

Server: Msg 8152, Level 16, State 9, Line 7

String or binary data would be truncated.

The statement has been terminated.

Another error?

raj-248044 SSC Eights! Points: 838 More actions · Answer 15

My USERID and BETTYPE are varchar?

Any problem with that??