StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POModify query sql
text
Body
copied!<p>I have a query to aggregate (compress) data from 1 min to any other time frame, and it works perfectly.</p> <pre><code>Use StockDataFromSella; DECLARE @D1 DateTime DECLARE @D2 DateTime DECLARE @Interval FLOAT SET @D1 = '2008-09-21T09:00:00.000' SET @D2 = '2010-10-20T17:30:00.000' SET @Interval = 15 ;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B), L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B), L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4), Ranges AS( SELECT DATEADD(MINUTE,@Interval*(i-1),@D1) AS StartRange, DATEADD(MINUTE,@Interval*i,@D1) AS NextRange FROM Nums where i <= 1+CEILING(DATEDIFF(MINUTE,@D1,@D2)/@Interval)) ,cte AS ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY Simbolo,r.StartRange ORDER BY [DataOra]) AS RN_ASC ,ROW_NUMBER() OVER (PARTITION BY Simbolo,r.StartRange ORDER BY [DataOra] DESC) AS RN_DESC FROM Ranges r JOIN dbo.tbl1MinENI p ON p.[DataOra] >= r.StartRange and p.[DataOra] < r.NextRange ) SELECT Simbolo, MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END) AS DataOra, MAX(CASE WHEN RN_ASC=1 THEN [Apertura] END) AS [Apertura], MAX(Massimo) Massimo, MIN(Minimo) Minimo, MAX(CASE WHEN RN_DESC=1 THEN [Chiusura] END) AS [Chiusura], SUM(Volume) Volume /*MAX(CASE WHEN RN_DESC=1 THEN [DataOra] END) AS ChiusuraDataOra*/ FROM cte GROUP BY Simbolo,StartRange ORDER BY DataOra </code></pre> <p>I would like to split second column DataOra(DateTime) into two diferent columns, one for Date (if possibile in dd/mm/yyyy format) and the second for Time only. Any help very appreciated, thanks. Alberto</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload