Arithmetic operator not working in joined table

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

Arithmetic operator not working in joined table

Author

Topic

budi
Starting Member

5 Posts

Posted - 2010-08-13 : 02:58:15

Dear All,

I need help for query in SQL Server 2005. I have 2 tables as below:

tb_comp_latest_change

PlantNo Component SMU
DR501 Engine 15600
DR520 Engine 7600
DR525 Engine 9800
DR525 Water Pump 9800

tb_comp_change

PlantNo Component ChangeoutSMU ChangeoutDate
DR501 Engine 10090 5/5/2008 0:00
DR525 Water Pump 7003 4/5/2007 0:00
DR501 Engine 12003 6/7/2009 0:00

I join these two tables with query below, with result if left column record did not found match in the right table, will show NULL value.

SELECT tb_comp_latest_change.PlantNo,
tb_comp_latest_change.SMU,
tb_comp_latest_change.Component,
(SELECT MAX(ChangeoutSMU) FROM tb_comp_change WHERE
tb_comp_latest_change.PlantNo = tb_comp_change.PlantNo AND
tb_comp_latest_change.Component = tb_comp_change.Component) AS LatestChangeSMU,
SELECT MAX(ChangeoutDate) FROM tb_comp_change WHERE
tb_comp_latest_change.PlantNo = tb_comp_change.PlantNo AND
tb_comp_latest_change.Component = tb_comp_change.Component) AS LatestChangeSMU,
(SMU-(SELECT MAX(ChangeoutSMU) FROM tb_comp_change WHERE
tb_comp_latest_change.PlantNo = tb_comp_change.PlantNo AND
tb_comp_latest_change.Component = tb_comp_change.Component)) AS LifeToDate

FROM tb_comp_latest_change

I add an expression column “LifeToDate” with formula (SMU – LastChangeSMU), but the result for DR520 engine and DR525 engine is NULL. I want the NULL value in ChangeoutSMU column to have value '0' (zero), So the result for DR520 engine and DR525 engine is 7600 and 9800.

Desired Output Table
PlantNo SMU Component ChangeoutSMU ChangeoutDate LifeToDate
DR501 15600 Engine 12003 6/7/2009 0:00 3597
DR520 7600 Engine NULL NULL 7600
DR525 9800 Engine NULL NULL 9800
DR525 9800 Water Pump 7003 4/5/2007 0:00 2797

Why arithmetic operator not working in joined table like this? Please help the solution, thanks

Salam,
Budi

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-13 : 08:25:02

SELECT l.plantno,l.smu,l.Component
, c.ChangeoutSMU
,c.changeoutdate
,l.smu - isnull(c.ChangeOutSmu,0) as LifeToDate
FROM
tb_comp_latest_change l
LEFT JOIN
(
select plantNo,Component,ChangeOutSmu,ChangeoutDate
,rank() Over (partition by plantNo,Component order by ChangeoutDate desc) as latest
from tb_comp_change
) c
ON l.plantno = c.plantno and l.component = c.component
and c.latest = 1

Jim

Everyday I learn something that somebody else already knew

budi
Starting Member

5 Posts

Posted - 2010-08-14 : 02:04:03

Thanks a lot Jimf, it's working

Salam,
Budi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources