Help with Query - 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 2005 Forums

Transact-SQL (2005)

Help with Query

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-05 : 06:13:59

I have 2 tables All loads, Balances with the following columns and data. ID is identity column.

All loads

ID CardProduct Amount
1 A 10
2 A 20
3 A 30
4 B 40
5 B 50
6 B 60
7 C 70
8 C 80
9 A 90

Balances

CardProduct Balance
A 500
B 600
C 400

I need to write a stored proc or function that sum up the amount based on the card product in the all loads table and updates in the balances table.
However next time it executes it should not choose the records that has already summed up. In other words only the new records has to be summed up.

Please help me with the query.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 06:26:17

do you've any other column in all loads table to mark that it has already been counted? if not, you need add one. else you will not be able to identify already counted ones and will end up double counting them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-05 : 06:33:45

I can not modify the structure of the table. What about having a lookup table with id and card product. So every time when the proc execute it inserts the max id for each card product. so that every time it executes it checks only the id greater than that value.
Is this a good approach?

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 06:36:44

quote:
Originally posted by sandesh.ravi

I can not modify the structure of the table. What about having a lookup table with id and card product. So every time when the proc execute it inserts the max id for each card product. so that every time it executes it checks only the id greater than that value.
Is this a good approach?

Thanks,
Sandesh

ok that should be better. dont put max id but rather put all ids that have been consolidated yet. and do a left join with it next time and look for nulls to exclude already inserted ones

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-05 : 06:39:23

Could you let me know the query please.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 07:28:58

[code]

update t
set t.Balance = t1.Tot
from [Balances] t
inner join (select CardProduct,sum(Amount) As Tot
from [All loads] a
left join newtable n
on n.ID = a.ID
and n.CardProduct = a.CardProduct
where n.ID is null
group by a.CardProduct)t1
on t1.CardProduct = t.CardProduct

insert into newtable
select columns...
from [All loads]

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-06 : 05:45:13

Thank you Visakh...

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 09:00:48

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources