| Author |
 Topic  |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
 Posted - 05/06/2012 : 08:16:57
|
Hi,
Hi,
I have two tables as follows:
CREATE TABLE [dbo].[LiveOdds](
[DrawNumber] [varchar](20) NULL,
[OddsRevision] [varchar](20) NULL,
[EventID] [varchar](20) NULL,
[EventDesc] [nvarchar](100) NULL,
[EventStatus] [nvarchar](30) NULL,
[OutComeID] [varchar](20) NULL,
[OutComeDesc] [nvarchar](100) NULL,
[OutComeGame] [nvarchar](100) NULL,
[OutComeGameCode] [varchar](50) NULL,
[OutComeOdd] [varchar](10) NULL
)
CREATE TABLE [dbo].[DrawProgramData](
[DrawNumber] [varchar](20) NULL,
[DataRevision] [varchar](20) NULL,
[EventID] [varchar](20) NULL,
[OutComeID] [varchar](20) NULL,
[OutComeDesc] [nvarchar](max) NULL,
[OutComeTeams] [nvarchar](100) NULL,
[OutComeGameDesc] [varchar](50) NULL,
[OutComeGame] [nvarchar](100) NULL,
[OutComeGameCode] [varchar](50) NULL,
[OutComeDate] [datetime] NULL,
[OutComeStartDate] [datetime] NULL,
[SE] [varchar](10) NULL,
[SpecialGameStatus] [nvarchar](30) NULL,
[SpecialCodeStatus] [nvarchar](30) NULL,
[RecordID] [int] NULL
)
Same numbers of rows are inserted into those tables and they are joined with draw number and event id.
I would like to get OutcomeDesc,OutcomeGame, OutcomeOdd. Here are the where clause requirements:
retrieve data for the max odds revision
retireve data for the closest OutcomeStartDate to today
<div>Best Regards.</div>
Edit1: Also I have to identify how many games are on, from event ids. (There may be more than one game, so every game has unique event id) |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/06/2012 : 13:22:55
|
something like
SELECT lo.*,dpd.*
FROM LiveOdds lo
INNER JOIN DrawProgramData dpd
ON dpd.DrawNumber =lo.DrawNumber
AND dpd.EventID = lo.EventID
INNER JOIN (SELECT DrawNumber,MAX(OddsRevision) AS OddsRevision
FROM LiveOdds
GROUP BY DrawNumber)lo1
ON lo1.DrawNumber = lo.DrawNumber
AND lo1.OddsRevision = lo.OddsRevision
If its not what you're looking for please post some sample data from tables and your expected output out of them
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 05/06/2012 : 14:07:01
|
| thanks visakh16, I will let you know. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/06/2012 : 14:11:25
|
wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 05/07/2012 : 06:16:22
|
Hi visakh16,
I want to combine results from two tables above and have the same number of records. (Each table has 18 num of rows so result should be 18 rows also, --distinct)
The results should have DrawNumber,EventId,OutComeDesc,OutComeGame,OutComeOdd. And always get the max OddsRevision from LiveOdds table.
Best Regards.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/07/2012 : 17:10:26
|
show some sample data and explain what you mean by duplicates. then I'll help you out. Unless I see some data i cant understand how tables are related, what according to you are duplicates etc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
How to query this?
