I think you misunderstand...
When I said what would be the expected result, I was wondering what columns each table you want to be returned by the SQL statement, including data that would be returned considering the sample data you posted.
with
CTE_Rev as
(
select
DrawNumber,
EventID,
MAX(OutComeDate) as MaxOutComeDate
from DrawProgramData
),
CTE_Start as
(
select
DrawNumber,
EventID,
OutComeStartDate,
ROW_NUMBER() OVER(PARTITION BY DrawNumber, EventID ORDER BY ABS(DATEDIFF(second, OutComeStartDate, GETDATE()))) AS RowNo
from DrawProgramData
)
select distinct
l.DrawNumber,
l.EventID,
l.OutcomeDesc,
l.OutcomeGame,
l.OutcomeOdd,
r.MaxOutComeDate,
s.OutComeStartDate
from LiveOdds as l
join CTE_Rev as r on (r.DrawNumber = l.DrawNumber) and (r.EventID = l.EventID)
join CTE_Start as s on (s.DrawNumber = l.DrawNumber) and (s.EventID = l.EventID) and (S.RowNo = 1)
Column 'DrawProgramProgram.DrawNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Keep your friends close and your enemies even closer
with
CTE_Rev as
(
select
DrawNumber,
EventID,
MAX(OutComeDate) as MaxOutComeDate
from DrawProgramData
group by DrawNumber, EventID
),
CTE_Start as
(
select
DrawNumber,
EventID,
OutComeStartDate,
ROW_NUMBER() OVER(PARTITION BY DrawNumber, EventID ORDER BY ABS(DATEDIFF(second, OutComeStartDate, GETDATE()))) AS RowNo
from DrawProgramData
)
select distinct
l.DrawNumber,
l.EventID,
l.OutcomeDesc,
l.OutcomeGame,
l.OutcomeOdd,
r.MaxOutComeDate,
s.OutComeStartDate
from LiveOdds as l
join CTE_Rev as r on (r.DrawNumber = l.DrawNumber) and (r.EventID = l.EventID)
join CTE_Start as s on (s.DrawNumber = l.DrawNumber) and
(s.EventID = l.EventID) and (S.RowNo = 1)
cenk1536
Contributor
2503 Points
2119 Posts
Query Help?
May 05, 2012 01:13 PM|LINK
Hi,
I have two tables as follows:
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)
gimimex
Participant
1052 Points
157 Posts
Re: Query Help?
May 05, 2012 02:00 PM|LINK
Hi,
You can post a sample of data from tables and the expected result?
cenk1536
Contributor
2503 Points
2119 Posts
Re: Query Help?
May 05, 2012 05:00 PM|LINK
Here is DrawProgram Table
gimimex
Participant
1052 Points
157 Posts
Re: Query Help?
May 05, 2012 08:34 PM|LINK
And what is the expected result for this sample data?
cenk1536
Contributor
2503 Points
2119 Posts
Re: Query Help?
May 06, 2012 12:19 PM|LINK
I am expecting from you :)
gimimex
Participant
1052 Points
157 Posts
Re: Query Help?
May 06, 2012 04:14 PM|LINK
I think you misunderstand...
When I said what would be the expected result, I was wondering what columns each table you want to be returned by the SQL statement, including data that would be returned considering the sample data you posted.
cenk1536
Contributor
2503 Points
2119 Posts
Re: Query Help?
May 06, 2012 04:34 PM|LINK
Here it is:
I would like to get OutcomeDesc,OutcomeGame, OutcomeOdd. Here are the where clause requirements:
<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)
gimimex
Participant
1052 Points
157 Posts
Re: Query Help?
May 06, 2012 09:25 PM|LINK
Try something like this:
with CTE_Rev as ( select DrawNumber, EventID, MAX(OutComeDate) as MaxOutComeDate from DrawProgramData ), CTE_Start as ( select DrawNumber, EventID, OutComeStartDate, ROW_NUMBER() OVER(PARTITION BY DrawNumber, EventID ORDER BY ABS(DATEDIFF(second, OutComeStartDate, GETDATE()))) AS RowNo from DrawProgramData ) select distinct l.DrawNumber, l.EventID, l.OutcomeDesc, l.OutcomeGame, l.OutcomeOdd, r.MaxOutComeDate, s.OutComeStartDate from LiveOdds as l join CTE_Rev as r on (r.DrawNumber = l.DrawNumber) and (r.EventID = l.EventID) join CTE_Start as s on (s.DrawNumber = l.DrawNumber) and (s.EventID = l.EventID) and (S.RowNo = 1)Hope it's useful.
cenk1536
Contributor
2503 Points
2119 Posts
Re: Query Help?
May 07, 2012 07:57 AM|LINK
Column 'DrawProgramProgram.DrawNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
gimimex
Participant
1052 Points
157 Posts
Re: Query Help?
May 07, 2012 11:23 AM|LINK
Try:
with CTE_Rev as ( select DrawNumber, EventID, MAX(OutComeDate) as MaxOutComeDate from DrawProgramData group by DrawNumber, EventID ), CTE_Start as ( select DrawNumber, EventID, OutComeStartDate, ROW_NUMBER() OVER(PARTITION BY DrawNumber, EventID ORDER BY ABS(DATEDIFF(second, OutComeStartDate, GETDATE()))) AS RowNo from DrawProgramData ) select distinct l.DrawNumber, l.EventID, l.OutcomeDesc, l.OutcomeGame, l.OutcomeOdd, r.MaxOutComeDate, s.OutComeStartDate from LiveOdds as l join CTE_Rev as r on (r.DrawNumber = l.DrawNumber) and (r.EventID = l.EventID) join CTE_Start as s on (s.DrawNumber = l.DrawNumber) and (s.EventID = l.EventID) and (S.RowNo = 1)Hope this helps.