SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query to New Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevieb
Starting Member

13 Posts

Posted - 03/06/2014 :  01:48:45  Show Profile  Reply with Quote
I am wondering if anyone can advise on a way to run a query. WE have been happy just running a query based on a simple average function and exporting this manually to a CSV File. But we are wanting things to become a little bit more autonomous.

Our Existing Query is

SELECT navteqid,
CAST(AVG(historicalspeed) as numeric(12,2))as avgspd_1
FROM fusedobservations_Jan14

WHERE
(dbtime > '16:00:00') AND
(dbtime < '16:15:01') AND
(dbDayofWeek = 1)
GROUP BY navteqid



We run this Query 4 times for each Quarter hour period and generate avgspd_1, avgspd_2, avgspd_3 and avgspd_4.

I want to update this so that a new table generated to include
navteqid, avgspd_1, avgspd_2, avgspd_3 and avgspd_4. I have looked at the SELECT* INTO function but i am unable to get this to work as i would like.








maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/06/2014 :  10:54:31  Show Profile  Reply with Quote
The following query might work. I haven't tested it.

SELECT navteqid,
( SELECT CAST(AVG(historicalspeed) as numeric(12,2) WHERE
(dbtime > '16:00:00') AND
(dbtime <= '16:15:00') AND
(dbDayofWeek = 1)) AS avgspd_1,
( SELECT CAST(AVG(historicalspeed) as numeric(12,2) WHERE
(dbtime > '16:15:00') AND
(dbtime <= '16:30:00') AND
(dbDayofWeek = 1)) AS avgspd_2,
( SELECT CAST(AVG(historicalspeed) as numeric(12,2) WHERE
(dbtime > '16:30:00') AND
(dbtime <= '16:45:00') AND
(dbDayofWeek = 1)) AS avgspd_3,
( SELECT CAST(AVG(historicalspeed) as numeric(12,2) WHERE
(dbtime > '16:45:00') AND
(dbtime <= '17:00:00') AND
(dbDayofWeek = 1)) AS avgspd_4
INTO new_table_name
FROM fusedobservations_Jan14

=======================
Not an Expert, Just a learner.
!_(M)_!

Edited by - maunishq on 03/06/2014 10:55:59
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/06/2014 :  12:32:04  Show Profile  Reply with Quote
I am not understanding the following part

"We run this Query 4 times for each Quarter hour period and generate avgspd_1, avgspd_2, avgspd_3 and avgspd_4."

each quarter hour, does it means to execute it every quarter e.g. on 1615, 1630 , 1645, and then on 1700 .. similarly for each hour of the day. Ultimately executing 96 times a day? And with same where condition? or the where condition would be different on each and every quarter?

In case if it is same (which I dont think make any sense) simply use the following

INSERT INTO aTableName (navteqid,avgspd_1)
SELECT navteqid, CAST(AVG(historicalspeed) as numeric(12,2))as avgspd_1
FROM fusedobservations_Jan14
WHERE
(dbtime > '16:00:00') AND
(dbtime < '16:15:01') AND
(dbDayofWeek = 1)
GROUP BY navteqid

SELECT navteqid,avgspd_1,avgspd_1 as avgspd_2,avgspd_1 as avgspd_3,...,avgspd_1 as avgspd_N
FROM aTableName

Cheers
MIK
Go to Top of Page

stevieb
Starting Member

13 Posts

Posted - 03/08/2014 :  23:55:01  Show Profile  Reply with Quote
MIK, Sorry we run the same type of query 4x for each hour we need to do analysis for.

so for 1600-1700 we run the query 4x with each 15 minute interval defined in the query.

We usually just export the results as a CSV.

But we are getting asked more and more to do a lot more analysis and map this results in a Geodatabase. So we would like to run the query and save them to a new Table so this table can be linked to the geodatabase.

maunishq, i am getting a syntax error for the WHERE command. I did try something similar but failed.
Thanks

STeve
Go to Top of Page

stevieb
Starting Member

13 Posts

Posted - 03/09/2014 :  00:51:46  Show Profile  Reply with Quote
I have changed the query to the below but it does not update the external table for all variables. Only avgspd_1 is updated, avgspd_2 is left as a NULL value

INSERT INTO db_analysis.dbo.Analysis
(navteqid, avgspd_1)
SELECT navteqid,
CAST(AVG(historicalspeed) as numeric(12,2)) avespd_1

FROM fusedobservations_Jan14

WHERE
(dbtime >= '16:00:00') AND
(dbtime < '16:15:00') AND
(dbDayofWeek =1)
group by navteqid

INSERT INTO db_analysis.dbo.Analysis
(navteqid, avgspd_2)

SELECT navteqid,
CAST(AVG(historicalspeed) as numeric(12,2))as avgspd_2

FROM fusedobservations_Jan14

WHERE
(dbtime >= '16:15:00') AND
(dbtime < '16:30:00') AND
(dbDayofWeek =1)
group by navteqid

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000