80352

My first complex SQL queries

Question:

I've been writing a C# program to create a database in SQL Server from imported .csv data files. I've now got the database working and need to make some fairly complex (for me) queries to it. For the problem that I'm currently working on I have these queries:

SELECT MatchID, DateTimeKO = DateKickOff + CAST(TimeKickOff AS DATETIME) FROM Match WHERE MatchID = 9000

which returns:

MatchID DateTimeKO 9000 2011-09-17 14:00:00.000

and:

(SELECT MatchID, OddsFirstTimeTaken AS WhenOddsTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched,InPlay FROM Data as D WHERE MatchID = 9000 AND MarketName = 'Match Odds' AND Outcome = 'The Draw' UNION --ALL SELECT MatchID, OddsLastTimeTaken AS WhenOddsTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched,InPlay FROM Data as D WHERE MatchID = 9000 AND MarketName = 'Match Odds' AND Outcome = 'The Draw' )ORDER BY WhenOddsTaken

which returns:

MatchID WhenOddsTaken MarketName Outcome Odds NumberOfBets VolumeMatched InPlay 9000 2011-09-17 13:58:17.000 Match Odds The Draw 3.80 243 15997.30 0 9000 2011-09-17 13:59:14.000 Match Odds The Draw 3.90 39 1923.44 0 9000 2011-09-17 13:59:31.000 Match Odds The Draw 3.95 8 209.12 0 9000 2011-09-17 13:59:44.000 Match Odds The Draw 3.85 106 5740.90 0 9000 2011-09-17 14:00:17.000 Match Odds The Draw 3.90 33 628.26 1 9000 2011-09-17 14:00:24.000 Match Odds The Draw 4.10 2 2.70 1 9000 2011-09-17 14:00:29.000 Match Odds The Draw 3.85 53 693.46 1 9000 2011-09-17 14:01:02.000 Match Odds The Draw 3.95 2 94.28 1 9000 2011-09-17 14:01:24.000 Match Odds The Draw 3.80 69 3988.24 1 9000 2011-09-17 14:04:05.000 Match Odds The Draw 3.90 33 628.26 1 9000 2011-09-17 14:05:22.000 Match Odds The Draw 3.75 20 1873.80 1

What I would now like to do is add another where condition that returns the data from the 2nd query, only if the 'WhenOddsTaken' datetime is greater than the value of 'DateTimeKO' in the first query. i.e. it should return:

MatchID WhenOddsTaken MarketName Outcome Odds NumberOfBets VolumeMatched InPlay 9000 2011-09-17 14:00:17.000 Match Odds The Draw 3.90 33 628.26 1 9000 2011-09-17 14:00:24.000 Match Odds The Draw 4.10 2 2.70 1 9000 2011-09-17 14:00:29.000 Match Odds The Draw 3.85 53 693.46 1 9000 2011-09-17 14:01:02.000 Match Odds The Draw 3.95 2 94.28 1 9000 2011-09-17 14:01:24.000 Match Odds The Draw 3.80 69 3988.24 1 9000 2011-09-17 14:04:05.000 Match Odds The Draw 3.90 33 628.26 1 9000 2011-09-17 14:05:22.000 Match Odds The Draw 3.75 20 1873.80 1

Additionally to this I would like a further query to return the first line of data that occurs for a certain time after DateTimeKO, e.g. DateTimeKO plus 2 minutes (first line after 2011-09-17 14:02:00.000) would return:

MatchID WhenOddsTaken MarketName Outcome Odds NumberOfBets VolumeMatched InPlay 9000 2011-09-17 14:04:05.000 Match Odds The Draw 3.90 33 628.26 1

I would appreciate any help. Thanks.

Answer1:

Don't have data to run it, but something along these lines:

with x as ( SELECT MatchID, DateTimeKO = DateKickOff + CAST(TimeKickOff AS DATETIME) FROM Match WHERE MatchID = 9000 ), y as ( SELECT MatchID, OddsFirstTimeTaken AS WhenOddsTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched,InPlay FROM Data as D WHERE MatchID = 9000 AND MarketName = 'Match Odds' AND Outcome = 'The Draw' UNION --ALL SELECT MatchID, OddsLastTimeTaken AS WhenOddsTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched,InPlay FROM Data as D WHERE MatchID = 9000 AND MarketName = 'Match Odds' AND Outcome = 'The Draw' ) select top (1) y.MatchID, WhenOddsTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched, InPlay from y inner join x on y.MatchID = x.MatchID where WhenOddsTaken > dateadd(minute, 2, DateTimeKO) order by WhenOddsTaken asc

Recommend

  • Ajax with slide effects onready witout using a toolkit
  • Does the order of columns matter in a multi-column index if there is no value in the WHERE clause
  • SED adds new line at the end
  • XPath select element based on other elements in node
  • WCF Data Services join query
  • convert fifo systemC program to PROMELA language with safety properties and liveness property
  • Mysql select count per category per day
  • Collection was modified. Enumeration operation may not execute
  • Picture entropy calculation
  • How to archive files older than 7 days with 7-ZIP by creating one archive for all files with same da
  • Compiling CUDA program
  • is it safe to use “ls” in for loop in bash
  • Write text on image as display as image
  • read a .bmp image using od command
  • Zxing project as library in a project won't build
  • How to alter a value before storing it to the database in Keystone JS
  • Best practice zend framework + faceBook Api
  • How can I use activerecord's query interface and scopes to identify the favorite in a race
  • How to split string in XML
  • Dependency Injector Classes: Static Singleton vs Parameter [closed]
  • How to access global variable value in multiple template tags
  • Why do I get a “connection is already open” error during a MySQL update statement?
  • Retrieve Greek letters from MSSQL database with PHP
  • getting swf version info
  • How to estimate GPU memory requirements for thrust based implementation?
  • PostgreSQL GROUP BY: SELECT column on MAX of another WHERE a third column = x
  • Setting date in nodejs date object shows date as different value
  • How to randomly select values for a list?
  • Json.Net TypeNameHandling.Auto and Asp Web Api Controller gives unexpected behaviour
  • MongoDB shell script using projection to format date and get local time
  • Inheriting code-behind class from PhoneApplicationPage's subclass
  • How can I write a where clause in SQL to filter a DATETIME column by the time of day?
  • Comparing elements in two lists when keeping duplicates is desired in Python
  • Replace any string in columns with 1
  • f:param to composite components
  • Passing “get” parameters doesn't work, parameter not visible in the link
  • embed rChart in Markdown
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app