Modify query sql

Question

I have a query to aggregate (compress) data from 1 min to any other time frame, and it works perfectly.

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

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

Microsoft SQL Server Management Studio Express (SQL Server 2005) — Alberto acepsut, Jun 06 '11 at 21:01

dpmattingly · Accepted Answer · 2011-06-06T21:16:30.510

2

The easiest way to get these values would be to use CONVERT. SQL Server has some built-in date formatting when you convert a Date.

 CONVERT(VARCHAR, MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END), 103) AS DataOraDate, 
 CONVERT(VARCHAR, MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END), 114) AS DataOraTime,

The two codes (103 and 114) correspond to the formats that you want. See http://msdn.microsoft.com/en-us/library/ms187928.aspx for more details about those and the other formats you can convert to.

edited Jun 06 '11 at 21:16

answered Jun 06 '11 at 21:03

dpmattingly

1,301
1
7
11

@dpmattingly I Thought the same like you, but i think it wont work at all, because the select will not do the max from the entire datetime, it will do the max just comparing the date without the time in one column, and in the other one just with the time and no comparing the date. – Hector Sanchez Jun 06 '11 at 21:10
1

You're right, you need to bring the CONVERT outside of the MAX to make it work. I've switched it around to make it less confusing, but I think makes it the same as your answer. – dpmattingly Jun 06 '11 at 21:17
Thanks, it works, I have two splitted columns for Date and Time: I think there must be something better than 103 for Date. I set ORDER BY DataOraDate and I have day 1 then months from 1 to 12, day 2 then months from 1 to 12 and so on.I should have day from 1 to 30 (31) with month 1, and so on.Thanks – Alberto acepsut Jun 06 '11 at 21:26
1

Just order them by the real oradate, without convert it, to get the all datetime, it should work. – Hector Sanchez Jun 06 '11 at 21:34
If I set ORDER BY DataOra I get errors: columns name"cte.DataOra"is not valid in ORDER BY since there isn't in any aggregation function nor in GROUP BY – Alberto acepsut Jun 06 '11 at 21:50
You have to add it into the select clause select MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END) AS DataOra – Hector Sanchez Jun 06 '11 at 21:53
Thanks Mr. now I have Date shown correctly, but of course if I add it into the SELECT I have also this column printed, while I do not need to have the original DataOra column, just the Data and Time splitted columns. – Alberto acepsut Jun 06 '11 at 21:59
Well i cant figure another way than make a subquery SELECT Simbolo, Apertura, Massimo, Minimo, Chiusura, Volume, DataOraDate, DataOraTime FROM (ALL THE QUERY THAT YOU MADE BEFORE) ORDER BY DataOra, I know it is not a best practice, but i cant find another way to meet your requierements. Hope it helps. – Hector Sanchez Jun 06 '11 at 22:09

Hector Sanchez · Answer 2 · 2011-06-06T21:29:17.203

2

As it seems you just only need to change the select. Instead of this

MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END) AS DataOra,

Use this:

CONVERT(VARCHAR(8),MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END),101) AS DateOnly,

Hope it helps.

edited Jun 06 '11 at 21:29

answered Jun 06 '11 at 21:06

Hector Sanchez

2,297
4
26
39

Modify query sql

2 Answers2