Need to replace null with data from another field

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)

Need to replace null with data from another field

Author

Topic

biogem
Starting Member

13 Posts

Posted - 2009-09-29 : 15:50:07

DECLARE @Date DATETIME
SET @Date = GETDATE()

SELECT
case
WHEN LEAS.MOCCPID IN (SELECT * FROM intercompanyMOCCPIDS) THEN 'InterEntity'
WHEN LEAS.BLDGID in (SELECT BLDGIDSHORT FROM CustomerBLDGNames) then '(select bldgname from CustomerBLDGNames)'
when LEAS.bldgid in ('5150A', '5150B') then 'CRG LLC'
when LEAS.bldgid between '5200A' and '5200C' then 'K Street'
when LEAS.bldgid Between'5210A' and '5210m' then 'One Wilshire'
when LEAS.bldgid = '5220A' then 'A of A'
when LEAS.bldgid between '5549A' and '5549O' then 'OW - OLD'
when LEAS.bldgid between '8035A' and '8040F' then 'MPT'
when LEAS.bldgid between '8200A'and '8200C' then 'Miami'
when LEAS.bldgid between '8250A' and '8255C' then 'Wilshire Annex'
when LEAS.bldgid = '8300A' then 'Valley'
when LEAS.bldgid between '8350A' and '8355B' then 'LaSalle'
when LEAS.bldgid between '8400A' and '8405A' then 'Boston'
when LEAS.bldgid between '8450A' and '8455A' then 'Reston'
when LEAS.bldgid between '8500A' and '8505A' then 'Coronado'
end as BUILDING,

CASE
WHEN Datediff(day, CMLEDG.TRANDATE, getdate()) <= '30' THEN '0 - Current'
WHEN Datediff(day, CMLEDG.TRANDATE, getdate()) BETWEEN '31' AND '59' THEN '1 Month'
WHEN Datediff(day, CMLEDG.TRANDATE, getdate()) BETWEEN '60' AND '89' THEN '2 Months'
WHEN Datediff(day, CMLEDG.TRANDATE, getdate()) BETWEEN '90' AND '119' THEN '3 Months'
WHEN Datediff(day, CMLEDG.TRANDATE, getdate()) >= 120 THEN '4 Months' END AS STATUS, datediff(dd,trandate, GETDATE() )AS [AGE DATE],
LEAS.TENTID,
LEAS.OCCPNAME, CMLEDG.TRANDATE, CMLEDG.INCCAT, (SELECT INCH.DESCRPTN FROM INCH WHERE INCH.INCCAT=CMLEDG.INCCAT) AS [INCOME CAT],

(SELECT TENT.TENTNAME
FROM TENT WHERE TENT.NTENTID = LEAS.TENTID) AS [TENTNAME],
CASE WHEN TENTNAME IS NULL THEN LEAS.OCCPNAME END AS [TENTNAME],

CMLEDG.SRCCODE, CMLEDG.TRANAMT, CMLEDG.TRANAMT - COALESCE((SELECT SUM(AMT) FROM CMLEDGAPPLY,CMLEDG AS B
WHERE CMLEDG.TRANID=CMLEDGAPPLY.TRANID AND B.TRANID=CMLEDGAPPLY.PTRANID AND B.TRANDATE <= @Date ),0)+
COALESCE((SELECT SUM(AMT) FROM CMLEDGAPPLY, CMLEDG C WHERE CMLEDG.TRANID=CMLEDGAPPLY.PTRANID
AND C.TRANID=CMLEDGAPPLY.TRANID AND C.TRANDATE <= @Date ),0),CMLEDG.PERIOD, LEAS.MOCCPID, LEAS.GENERATION,
LEAS.DAYDUE, LEAS.DELQDAY, LEAS.OCCPSTAT, LEAS.BLDGID, LEAS.LEASID, LEAS.ADDLSPACE FROM CMLEDG (NOLOCK), LEAS (NOLOCK)
WHERE CMLEDG.TRANAMT - COALESCE((SELECT SUM(AMT) FROM CMLEDGAPPLY, CMLEDG AS B
WHERE CMLEDG.TRANID=CMLEDGAPPLY.TRANID AND B.TRANID=CMLEDGAPPLY.PTRANID AND B.TRANDATE <= @Date ),0)+
COALESCE((SELECT SUM(AMT) FROM CMLEDGAPPLY, CMLEDG AS C WHERE CMLEDG.TRANID=CMLEDGAPPLY.PTRANID
AND C.TRANID=CMLEDGAPPLY.TRANID AND C.TRANDATE <= @Date ),0) <> 0 AND TRANDATE <= @Date
AND LEAS.BLDGID=CMLEDG.BLDGID AND LEAS.LEASID=CMLEDG.LEASID --AND LEAS.TENTID = TENT.NTENTID
ORDER BY TENTNAME, LEAS.MOCCPID,LEAS.GENERATION,CMLEDG.TRANDATE, CMLEDG.INCCAT, CMLEDG.SRCCODE

The problem area is the ((SELECT TENT.TENTNAME
FROM TENT WHERE TENT.NTENTID = LEAS.TENTID) AS [P.TENTNAME],
CASE WHEN P.TENTNAME IS NULL THEN LEAS.OCCPNAME END AS [TENTNAME],) area I can't figure this out, I need to have the the null replaced with the value of occpname if null, if not then use the tentname data.

Thanks.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-29 : 16:27:36

does
CASE WHEN (SELECT TENT.TENTNAME
FROM TENT WHERE TENT.NTENTID = LEAS.TENTID) IS NULL THEN LEAS.OCCPNAME
ELSE (SELECT TENT.TENTNAME
FROM TENT WHERE TENT.NTENTID = LEAS.TENTID) END AS [TENTNAME],

help?

biogem
Starting Member

13 Posts

Posted - 2009-09-29 : 16:31:58

That works, thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources