CODEWITHSUNDEEP
sql-servergroup-by
andreapier
andreapier

Reputation: 2958

Aggregate by date sql server 2005

i have a table with columns:

ID_LOG (this is the pk) | TS (type=datetime) | VALUE (type=double) | ID_CHANNEL (unique).

This table logs values form different channels every 15 minutes. The values are taken from some counters so they are always increasing.
Now i want to get the differences between maximum value and minimum value (so the net difference) of every day of last year of a specific channel.
Something like:

SELECT MAX(T1.value) - MIN(T1.value), T1.ts
FROM dbo.[LOG] as T1
WHERE T1.ts > DATEADD(year, -1, GETDATE()) and
T1.ID_CHANNEL=x      //[x being the channel i want]
GROUP BY SOMEDATEFUNCITON(T1.ts) //[SOMEDATEFUNCTION is the function that groups results by date not taking in consideration the time of timestamp but only year-month-day]

I don't care about efficency as i will do this query only once or twice per year. Is this possible? And how you'd do?
Thanks a lot!

Upvotes: 0

Views: 927

Answers (2)

Niranjan Singh
Niranjan Singh

Reputation: 18290

SELECT convert(char(10), creationDate, 120), COUNT(custID) AS totalRegistered
FROM dbo.SecureSignInUser
WHERE (CreationDate BETWEEN '11/1/2004' AND '11/30/2004'

GROUP BY convert(char(10), creationDate, 120)

check this for your date format .. http://msdn.microsoft.com/en-us/library/ms187928.aspx

cast/ convert your function return date value format according to your database date format.

Upvotes: 1

ta.speot.is
ta.speot.is

Reputation: 27214

You can use CAST(T1.ts AS DATE) to truncate the timestamp. Substituting in to what you have:

SELECT MAX(T1.valore) - MIN(T1.valore), CAST(T1.ts AS DATE)
...
GROUP BY CAST(T1.ts AS DATE)

Although according to your schema, TS is already DATE, so you shouldn't need to do this. Did you mean to tell us that TS is a DATETIME?

Upvotes: 1

Related Questions