SQL Query Again - Complex
-
No. Just make sure they are the same size as the source columns in the Bets table.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks alot...Its working..Woohoo...
I do not how to thank you guys for all the help you have put. I have definitely learned alot from this, having Select Case statements in SQL. Wow...
Thanks once again
-
DECLARE @Win1 int, @Win2 int, @Win3 int, @Win4 int, @Win5 int
SELECT @Win1 = 1, @Win2 = 4, @Win3 = 7, @Win4 = 19, @Win5 = 33
INSERT INTO INTO MatchingBets
(BetID,DRAWIDmUSERID,BETTYPE,Matches,Mystery)
SELECT BetID,DRAWID,USERID,BETTYPE,0,0
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
Sorry guys once again. I have came across Stored Procedures in SQL, and learned that it greatly improves the perfromance. Since we just need to specify certain parameters for the stored procedure and it will execute the SQL query for us.
@Win1 @Win2 these are numbers from my DRAW TABLE. And I have included a Draw ID too in my tables.
So how do i make my @Win1... To reference my DRAW TABLE.
And How do I write my Store Procedure to Take in just my DRAWID as a Parameter for it to execute the queries. Since when I enter my DrawID if will get the winning numbers from my DrawTable, and Start quering my Bets table for that particular draw. Since Im working on ASP.net I can simply specify my DrawID and will execute my Stored Procedure.
Thanks alot in advance.
-
raj - not sure if this is what you're looking for since I'm not sure what the schema of your Draw table is...but this should at least get you started...
CREATE PROCEDURE upWinningNumbers
@DrawID Int
AS
DECLARE @Win1Int
DECLARE @Win2Int
DECLARE @Win3Int
DECLARE @Win4Int
DECLARE @Win5Int
BEGIN
SELECT @Win1 = Col1, @Win2 = Col2, @Win3 = Col3, @Win4 = Col4, @Win5 = Col5 FROM tblDraw WHERE DrawID = @DrawID
--then you would do your—
INSERT INTO INTO MatchingBets
(BetID,DRAWIDmUSERID,BETTYPE,Matches,Mystery)
SELECT BetID,DRAWID,USERID,BETTYPE,0,0
FROM Bets
UPDATE #MatchingBets
SET Matches =
(CASE WHEN NUM1 IN (@Win1,@Win2,@Win3,@Win4,@Win5) THEN 1 ELSE 0 END)…………..etc…..
END
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks sushila, i had solved that problem earlier...
-
CREATE PROCEDURE GetWinners (@DRAWID int, @Group1 int output, @Group2 int output, @Group3 int output, @Group4 int output, @Group5 int output
as
SELECT @Group1 = (SELECT COUNT (*) AS "Grp1" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '6')
SELECT @Group2 = (SELECT COUNT (*) AS "Grp2" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '5' AND MYSTERY = '1')
SELECT @Group3 = (SELECT COUNT (*) AS "Grp3" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '5')
SELECT @Group4 = (SELECT COUNT (*) AS "Grp4" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '4' AND MYSTERY = '1')
SELECT @Group5 = (SELECT COUNT (*) AS "Grp5" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '4')
SELECT @Group6 = (SELECT COUNT (*) AS "Grp6" FROM TEMPBETS WHERE DRAWID = @DRAW_ID AND MATCHES = '3' MYSTERY = '1')
GO
Is this Stored Procedure right?? Since I am doing a Count of the number of records for Different Groups of Winners?
Since in my ASP.net, im not getting any values from my Stored Procedure??
-
Have you defined the parameters as output?
When you execute this in QA do you get any results?
Far away is close at hand in the images of elsewhere.
Anon. -
Yes, finally manage to get it working..
-
I need help on another Stored Procedure.
SELECT BETTYPE FROM TempBets WHERE Matches = '3' and Mystery = '1'
Now there are a few Conditons(if statements)
IF BETTYPE = 'OD' THEN
@AMOUNT = '20'
@status = 'N'
INSERT RECORD into another WINNER Table.
INSERT USERID,AMOUNT,STATUS into Winner Table?
WINNER table has these Columns :
V_ID (uniqueindentifier)
USERID
AMOUNT
STATUS
How do I go about doing this..
-
try tweaking this around...I don't have time to test:
insert into Winner(Amount, Status)
select Amount = case
when betType = 'OD' then 20
when betType = 'OC' then 10 etc....
end,
Status = case
when betType = 'OD' then 'N'
when betType = 'OC' then 'Y' etc....
end
from
TempBets
where Matches = '3' and Mystery = '1'
**ASCII stupid question, get a stupid ANSI !!!** -
i think u got it wrong...I need to add in USERID,AMOUNT,STATUS.
Amount will vary based on condition. STATUS is only 'N'.
-
raj - i just threw in the other stuff IN CASE you had more conditions...
where are you getting the V_ID (is this an identity column ?!) & USERID from ?!
**ASCII stupid question, get a stupid ANSI !!!** -
V_ID is a uniqueindentifier column - which will increment on its own.
USERID - getting that from TempBets
-
ok - then try this: (assuming status is ALWAYS 'N' & only Amount changes based on betType....)
insert into Winner(UserID, Amount, Status)
select UserID,
Amount = case
when betType = 'OD' then 20
when betType = 'OC' then 10 etc....
end,
'N'
from
TempBets
where Matches = '3' and Mystery = '1'
**ASCII stupid question, get a stupid ANSI !!!** -
CREATE PROCEDURE CalculatePrize @DrawID int AS
DECLARE @Grp1 bigint, @Grp2 bigint, @Grp3 bigint, @Grp4 bigint, @Grp5 bigint,@Grp6 bigint, @Amt bigint
SELECT @Grp1 = SHARE1, @Grp2 = SHARE2, @Grp3 = SHARE3, @Grp4 = SHARE4, @Grp5 = SHARE5, @Grp6 = SHARE6
FROM SHARE WHERE DRAWID =@Draw_ID
INSERT INTO WINNERS
(USERID, AMOUNT, STATUS)
SELECT USERID,
AMOUNT = CASE
WHEN BETTYPE = 'OD' THEN @Grp4
WHEN BETTYPE = '7' THEN @Grp4 + 10
WHEN BETTYPE = '8' THEN @Grp4 + 30
WHEN BETTYPE = '9' THEN @Grp4 + 60
WHEN BETTYPE = '10' THEN @Grp4 + 100
WHEN BETTYPE = '11' THEN @Grp4 + 150
WHEN BETTYPE = '12' THEN @Grp4 + 210
END,
'N'
FROM TEMPBETS
WHERE MATCHES ='4' AND MYSTERY = '1'
GO
I did this procedure. But my calculations are wrong? I got 5 winning records, but my Winner Table has 13 records addded??
Viewing 15 posts - 46 through 60 (of 78 total)
You must be logged in to reply to this topic. Login to reply