Microsoft SQL 2 tables alias GROUP BY questions

[vladfrenkel]

Hi, I got the following question.
I got 2 tables in Lets say A and B
Structures of the tables are:
"A" table "B" Table
OldName Number New_name Number Value Time

Time field in "B" table in following format "YYYY-MM-DD HH24:MI:SS"
So knowing the alias I can easily create a query:
select A.New_Name,A.Old_Name,B.Value,B.Time from A,B where A.Number = B.Number AND B.Time BETWEEN '2003-12-01' AND '2004-01-30' AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%');
Everything worked so far. But, cause Value probe in "B" table happening every 3 minutes, and Name entries are about 10000 in the table,identified by '%mv" and '%mw', I simply cannot get some much data (imaging 60 days * 60min/3min * 24 * 10000 and you will get the magic number of rows. Tried to run it throught Database client or Vbscript ADO connections it is taking about 4 hours to complete. Cause I'm having 20 tables like that on 20 independent servers it is simply unacceptable for a time frame. So, we dicided instead of each minute to get an average for an each hour. I cannot get an average throught Vbscript (programmatically), cause it will not improve the database dump performance, I've to do it throught SQL. And here is the problem, I'm very familiar with MySQL logic and syntaksis, but this is my first time I'm querying Microsoft SQL server. Tried to construct the query - didn't work.
So result of my query should look like that
Time Name Value
01-30-04 00:00 abbmw 10
01-30-04 01:00 bbccmv 20
01-30-04 02:00 cccmvar 30
and so on.
Can somebody help me with this ?
Thank you very much.

Sincerely
vladfrenkel

 

[swampBoogie]

select A.New_Name,A.Old_Name,avg(B.Value), '2003-12-01' + right('00' + cast(hour(B.Time) as varchar(2)),2) + ':00'
from A,B 
where A.Number = B.Number 
AND B.Time BETWEEN '2003-12-01' AND '2004-01-30' 
AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%')
group by cast(hour(b.time) as varchar(2))

What indexes do you have?

 

[vladfrenkel]

Thank you very much for your response. I'll try this first thing at Monday. I'm not sure about indexes, cause Friday was the first time I saw the DB.
Thanks a lot
Sincerely
vladfrenkel

 

[vladfrenkel]

Hi,I've tryed that query. Received error message that HOUR( is not recognized as a function name...

Sincerely
vladfrenkel

 

[swampBoogie]

Oh, sorry about that. Some other DBMS has support for the hour function. SQL server uses datepart.

select A.New_Name,A.Old_Name,avg(B.Value), '2003-12-01' + right('00' + cast(datepart(hour,B.Time) as varchar(2)),2) + ':00'
from A,B
where A.Number = B.Number
AND B.Time BETWEEN '2003-12-01' AND '2004-01-30'
AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%')
group by cast(datepart(hour,b.time) as varchar(2))

 

[vladfrenkel]

Tried the query that youmjust provided. Recieved an error message:
ODBC Error 8120: Column A.New_Name is invalid in the select list because it is not contained in either an aggreagate function or the GROUP BY clause.
Tried to do something stupedly simple:
select A.New_Name,A.Old_Name,avg(B.Value), '2003-12-01' + right('00' + cast(datepart(hour,B.Time) as varchar(2)),2) + ':00'
from A,B
where A.Number = B.Number
AND B.Time BETWEEN '2003-12-01' AND '2004-01-30'
AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%')
group by cast(datepart(hour,b.time) as varchar(2)),A.New_name
Got the sane error with complain on A.Old_Name
Added A.Old_Name to the GROUP BY clause like that:...

select A.New_Name,A.Old_Name,avg(B.Value), '2003-12-01' + right('00' + cast(datepart(hour,B.Time) as varchar(2)),2) + ':00'
from A,B
where A.Number = B.Number
AND B.Time BETWEEN '2003-12-01' AND '2004-01-30'
AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%')
group by cast(datepart(hour,b.time) as varchar(2)),A.New_name,A.Old_Name

And received the same error message complaining now about B.Time column, after I added B.Time column to GROUP BY clause like that:
select A.New_Name,A.Old_Name,avg(B.Value), '2003-12-01' + right('00' + cast(datepart(hour,B.Time) as varchar(2)),2) + ':00'
from A,B
where A.Number = B.Number
AND B.Time BETWEEN '2003-12-01' AND '2004-01-30'
AND (A.New_Name like '%mw%' OR A.New_Name like '%mv%')
group by cast(datepart(hour,b.time) as varchar(2)),A.New_name,A.Old_Name,B.Time

Error messages has stopped, but in response, I do not receive any data..., just empty list

What can it be ?

Sincerely
vladfrenkel