Update stmt - 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)

Update stmt

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-11-26 : 08:12:16

Hi pals,

I have a datawarehouse database in which two tables are there.
One is "Src" table and other "trg" serves as target table.

select * from src

ssn distcode fee dt
101 1 0 NULL
101 1 0 NULL
101 1 5 2007-11-26 00:00:00.000
101 1 10 2007-11-26 00:00:00.000

I loaded the ssn and repo
select * from trg
ssn distcode tot_fee
101 1 NULL

Now i need to update the total_fee field based on below conditions

inital calculation:
-------------------
sum of fee where "dt" is null group by ssn,distcode.

Note: is the above sum is zero then consider below secondary calculation

Secondary calculation:
------------------------
sum of "fee" where "dt" = max of dt in the "Src" table group by ssn,distcode

Note: is the sum is zero store zero itself.

for the above requirement i have used the below update statement. This is working fine.
If u observe the below query the "src" table is loading 3 times.
can we optimize this query? Can we avoid these multiple loading of the same table into memory?

---
update trg set total_fee =
(
select case sum(b.fee) when 0 then

--------- secondary condition
(select case sum(c.fee) when 0.0 then
0.0
else
sum(c.fee)
end
from src c
where c.dt = (select max(dt) from src)
and c.ssn=a.ssn
and c.distcode=a.distcode)
--------

else
sum(b.fee)
end
from src b
where b.dt is null
and b.ssn=a.ssn
and b.distcode=a.distcode
) from trg a

Any suggestions are greatly appreciated

Thanks and Regards,

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-26 : 23:53:47

Hi frank,

Please try with this

UPDATE trg SET total_fee = S.total_fee
FROM trg T
INNER JOIN
(
SELECT A.ssn,A.distcode,
COALESCE
(
-- FIRST CONDITION
(CASE WHEN
SUM(CASE WHEN A.dt IS NULL THEN fee ELSE 0 END) = 0 THEN NULL
ELSE SUM(CASE WHEN A.dt IS NULL THEN fee ELSE 0 END)
END),
-- SECOND CONDITION
(CASE WHEN
SUM(CASE WHEN M.dt IS NOT NULL THEN fee ELSE 0 END) = 0 THEN NULL
ELSE SUM(CASE WHEN M.dt IS NOT NULL THEN fee ELSE 0 END)
END),
-- LAST CONDITION
SUM(fee)
) AS total_fee
FROM src A
LEFT JOIN (SELECT ssn,distcode, MAX(dt) dt FROM src GROUP BY ssn,distcode) M
ON M.ssn = A.ssn AND M.distcode = A.distcode AND M.dt = A.dt
Group By A.ssn, A.distcode
) S ON S.ssn = T.ssn AND S.distcode = T.distcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources