SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
/* Scroll to bottom to write your queries */ CREATE TABLE [dbo].[Stage]( [sourceID] [varchar](200) NULL, [tradeDate] [varchar](200) NULL, [high] [varchar](200) NULL, [low] [varchar](200) NULL, [close] [varchar](200) NULL ); CREATE TABLE [dbo].[History]( [symbolID] [int] NOT NULL, [tradeDate] [datetime] NOT NULL, [high] [decimal](18, 6) NULL, [low] [decimal](18, 6) NULL, [close] [decimal](18, 6) NOT NULL ); CREATE TABLE [dbo].[MetaData]( [symbolID] [int] NOT NULL, [dataType] [varchar](100) NULL, [exchangeCode] [varchar](100) NULL, [sourceID] [varchar](200) NOT NULL ); INSERT INTO [Metadata]([symbolID],[dataType],[exchangeCode],[sourceID]) VALUES (1,'Electricity','TEST','LMP'), (2,'Oil','TEST','Brent'), (5,'RBOB','TEST','RBOB'), (7,'Electricity','TEST','LMP2'), (8,'OIL','TEST','LMP3'), (9,'Oil','TEST','Crude'); INSERT INTO [History]([symbolID],[tradeDate],[high],[low],[close]) VALUES (1,CAST('20111008 00:00:00.000' as DATETIME),906.000000,906.000000,906.000000), (1,CAST('20111009 00:00:00.000' as DATETIME),915.000000,915.000000,915.000000), (1,CAST('20121008 00:00:00.000' as DATETIME),1906.000000,1906.000000,1906.000000), (1,CAST('20121009 00:00:00.000' as DATETIME),1915.000000,1915.000000,1915.000000), (1,CAST('20121010 00:00:00.000' as DATETIME),1892.000000,1892.000000,1892.000000), (1,CAST('20121011 00:00:00.000' as DATETIME),1890.000000,1890.000000,1890.000000), (1,CAST('20121012 00:00:00.000' as DATETIME),1885.000000,1885.000000,1885.000000), (2,CAST('20121008 00:00:00.000' as DATETIME),1950.000000,1950.000000,1950.000000), (2,CAST('20121009 00:00:00.000' as DATETIME),1950.000000,1950.000000,1950.000000), (2,CAST('20121010 00:00:00.000' as DATETIME),1920.000000,1920.000000,1920.000000), (2,CAST('20121011 00:00:00.000' as DATETIME),1930.000000,1930.000000,1930.000000), (2,CAST('20121012 00:00:00.000' as DATETIME),1855.000000,1855.000000,1855.000000), (3,CAST('20121008 00:00:00.000' as DATETIME),581.310000,577.490000,579.030000), (3,CAST('20121009 00:00:00.000' as DATETIME),581.870000,578.810000,581.010000), (3,CAST('20121010 00:00:00.000' as DATETIME),581.190000,578.850000,579.080000), (3,CAST('20121011 00:00:00.000' as DATETIME),583.320000,578.240000,581.950000), (3,CAST('20121012 00:00:00.000' as DATETIME),581.000000,576.270000,577.000000); INSERT INTO [Stage]([sourceid],[tradeDate],[high],[low],[close]) VALUES ('Brent','Oct 10 2012 12:00PM','2009.90000000','2009.90000000','2009.90000000'), ('Brent','Oct 11 2012 12:00PM','1999.80000000','1999.80000000','1999.80000000'), ('Brent','Oct 12 2012 12:00PM','1855.00000000','1855.0000000','1855.00000000'), ('Brent','Oct 13 2012 12:00PM','2025.05000000','2025.05000000','2025.05000000'), ('Brent','Oct 16 2012 12:00PM','1989.70000000','1989.70000000','1989.70000000'), ('Brent','Oct 17 2012 12:00PM','2009.90000000','2009.90000000','2009.90000000'), ('Brent','Oct 18 2012 12:00PM','1994.75000000','1994.75000000','1994.75000000'), ('Brent','Oct 19 2012 12:00PM','1974.55000000','1974.55000000','1974.55000000'), ('Brent','Oct 20 2012 12:00PM','1999.80000000','1999.80000000','1999.80000000'), ('Brent','Oct 23 2012 12:00PM','1994.75000000','1994.75000000','1994.75000000'), ('Brent','Oct 24 2012 12:00PM','2004.85000000','2004.85000000','2004.85000000'), ('Brent','Oct 25 2012 12:00PM','2004.85000000','2004.85000000','2004.85000000'), ('Brent','Oct 26 2012 12:00PM','2009.90000000','2009.90000000','2009.90000000'), ('Brent','Oct 27 2012 12:00PM','2030.10000000','2030.10000000','2030.10000000'), ('Brent','Oct 30 2012 12:00PM','2004.85000000','2004.85000000','2004.85000000'), ('Brent','Nov 1 2012 12:00PM','2014.95000000','2014.95000000','2014.95000000'); /* Run any queries you want to below this comment, do not change any of the above code. Make sure MS SQL Server 2017 is the version selected, click on "Run SQL code" to see results on the right */ SELECT H.symbolID, M.dataType, H.tradeDate FROM HISTORY AS H LEFT JOIN ( SELECT * FROM METADATA) AS M ON H.symbolID = M.SymbolID GROUP BY H.symbolID ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear