Problem with a SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with a SQL

Author

Topic

akorahil
Starting Member

3 Posts

Posted - 2009-11-21 : 13:10:33

Hi,

I need obtain AVG results for each 3 days. The date field is a integer with the format yyyyMMddHHmmss.

Do you know how could i do it?

thanks a lot,

Marcos

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-21 : 14:54:10

SELECT AVG(SomeColHere)
FROM Table1
WHERE SomeOtherColHere >= 100000 * CONVERT(CHAR(8), GETDATE() - 3, 112) + REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '')

N 56°04'39.26"
E 12°55'05.63"

akorahil
Starting Member

3 Posts

Posted - 2009-11-22 : 03:33:50

Sorry, but i didn't say i'm working in SQLite and the function GETDATE() doesn't exist. Do you know if exists other way using only numbers? Now if I want look-up data of one year, i must ask to the database 122 times, and the connection to database is very slow.

more or less now is like this....

SELECT avg(field0) as avgf0
FROM table1
WHERE intmydate>=20061101000000
AND intmydate<20061104000000;

SELECT avg(field0) as avgf0
FROM table1
WHERE intmydate>=20061104000000
AND intmydate<20061107000000;

.....

thanks a lot, again

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-22 : 10:59:37

which sql server are you using??

Peets

akorahil
Starting Member

3 Posts

Posted - 2009-11-22 : 11:02:25

it's not sql server, it's SQLite

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-22 : 15:21:14

Something similar to

SELECT	AVG(SomeColHere)
FROM	Table1
WHERE	SomeOtherColHere >= strftime('%Y%m%d%H%M%S', 'now')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources