SQL Query to New Table - 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

SQL Query to New Table

Author

Topic

stevieb
Starting Member

13 Posts

Posted - 2014-03-06 : 01:48:45

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

71 Posts

Posted - 2014-03-06 : 10:54:31

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)_!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-06 : 12:32:04

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

stevieb
Starting Member

13 Posts

Posted - 2014-03-08 : 23:55:01

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

stevieb
Starting Member

13 Posts

Posted - 2014-03-09 : 00:51:46

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources