How to query this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to query this?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-05-06 : 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

52326 Posts

Posted - 2012-05-06 : 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

260 Posts

Posted - 2012-05-06 : 14:07:01

thanks visakh16, I will let you know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 14:11:25

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-05-07 : 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

52326 Posts

Posted - 2012-05-07 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources