aggregrate problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

aggregrate problem

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-07 : 11:47:53

hi

i have table t with fields customer,customer nbr,dateofregister.
i back up this table as another temptable t1.

But i want to back up this table only in incremental way i.e

i want to take the max dateof register in both the tables and if max(dateof register) of t is greater than max(dateofregister)t1 i would populate the field.

my query is as follows

insert into t1(fields.....)
select customer,customer_nbr,dateofregister
from t where t.max(dateofregister)>(select max(dateofregister) from t1)
group by customer,customer_nbr,dateofregister

when i use this i get this error
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

what is the correct way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 13:14:25

The problem here is the group by clause. Why are you using group by here? Also how do you handle updates & deletes happening to your table? or is it that you only perform inserts to it?

the better approach will be to store a datefield in your main table say datecreated. Each date you extract the records with datecreated >lastdaysdate and perform comparison with temptable. Three conditions can occur
1. A record in main table but not in temp table which means a new record so insert
2. A record in both the tables which means updates
3. A record not in main table but in temp which means its deleted.

something like

INSERT INTO Temp (Temp_PK,other fields...)
SELECT PK,other fields
FROM table t
LEFT JOIN temp tmp
on tmp.Temp_PK = t.PK
WHERE t.datecreated > dateadd(d,-1,dateadd(d,datediff(d,0,getdate()),0))
AND tmp.Temp_PK IS NULL

UPDATE tmp
SET tmp.fields=t.fields
FROM Temp tmp
INNER JOIN table t
ON tmp.Temp_PK = t.PK
WHERE t.datecreated > dateadd(d,-1,dateadd(d,datediff(d,0,getdate()),0))

DELETE tmp
FROM Temp tmp
LEFT JOIN table t
ON tmp.Temp_PK = t.PK
WHERE t.PK IS NULL

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-07 : 18:47:55

i only have to do insert . no need to update and delete. thats taken care in the main table located on the remote server.

thanks for ur respone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 12:07:50

quote:
Originally posted by akpaga

i only have to do insert . no need to update and delete. thats taken care in the main table located on the remote server.

thanks for ur respone

I see. b/w do you have a date valued audit field in your table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources