How to get rid of Nulls and keep the original rec? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to get rid of Nulls and keep the original rec?

Author

Topic

HelalM
Starting Member

19 Posts

Posted - 2010-08-25 : 12:10:15

The following query extract Mom's info (lets say 100 for Aug 2010).

--------------------------------------------------------------------------------
/**********************************************************************************/
if object_id('tempdb..#NewBaby1') is not null begin drop table #NewBaby1 end
/**********************************************************************************/

DECLARE
@RptStartDt varchar(20),
@RptEndDt varchar(20)

SET @RptStartDt = '05/31/2010 00:00:00'
SET @RptEndDt = '06/06/2010 23:59:01'

SELECT Distinct
RTRIM(m.firstname) + SPACE(1) + RTRIM(m.middlename) + SPACE(1) + LTRIM(m.lastname) as MomFullName
,m.memid as MomMemID
,e.carriermemid as MomID
,'MomSubID' = Case When rf.attributeid ='C02597705'
Then rf.thevalue
End

,'BabyGender' = Case When rf.attributeid ='C02597699'
and rf.thevalue like 'F%' Then 'Girl'
when rf.attributeid ='C02597699'
and rf.thevalue like 'M%' Then 'Boy'
End

,'BabyWeight' = Case When rf.attributeid ='C02597689'
Then rf.thevalue
End

,'BabyWeightCat' = Case When rf.attributeid ='C02597689'
and rf.thevalue > '2500' Then 'Normal Weight'
when rf.attributeid ='C02597689'
and rf.thevalue between '1250' and '2500' Then 'Low Birth Weight'
when rf.attributeid ='C02597689'
and rf.thevalue < '1250' Then 'Extremely Low Birth Weight'
End

,'TypeDelivery' = Case When rf.attributeid ='C02597703'
and rf.thevalue like 'C%' Then 'C-Section'
when rf.attributeid ='C02597703'
and rf.thevalue like 'V%' Then 'Vaginal'
End

,'GestationalAge' = Case When rf.attributeid ='C02597701'
Then rf.thevalue
End

,'EDC' = Case When rf.attributeid ='C02597696'
Then rf.thevalue
End

,'BabyDob' = Case When rf.attributeid ='C02597711'
Then rf.thevalue
End

,'RateCode' = Case When rf.attributeid ='C02597709'
and rf.thevalue like 'P%' Then 'P NN'
End

,'Plans' = case when (b.description) like '%URBAN%'
Then 'Urban'
Else 'Rural'
End

,RTRIM(pto.fullname) as FacilName

INTO #NewBaby1
FROM memberview m with (nolock)
join referral r with (nolock) on m.memid=r.memid
join enrollkeys e with (nolock) on m.memid=e.memid
and e.enrollid = r.enrollid
left outer join referralattribute rf with (nolock) on rf.referralid = r.referralid
left outer join qattribute q with (nolock) on rf.attributeid = q.attributeid
--left outer join ReferralSvcView rv with (nolock) on r.referralid=rv.referralid
join benefitplan b with (nolock) on e.planid=b.planid
join provider pto with (nolock) on r.referto = pto.provid
Where q.attributeid in ('C02597696' , 'C02597699' , 'C02597701' , 'C02597711' , 'C02597703' , 'C02597709' , 'C02597705' , 'C02597689')
and r.createdate>= @RptStartDt and r.createdate <= @RptEndDt
Group by m.firstname
,m.middlename
,m.lastname
,m.memid
,e.carriermemid
,rf.attributeid
,rf.thevalue
,pto.fullname
,b.description
--Select * from #NewBaby1

IF OBJECT_ID('tempdb..#NewBaby2') IS NOT NULL BEGIN DROP TABLE #NewBaby2 END

CREATE TABLE #NewBaby2
(MomFullName varchar(60)
,MomID varchar(25)
,MomMemID varchar (25)
,MomSubID varchar(25)
,BabyGender varchar(10)
,BabyWeight varchar (25)
,BabyWeightCat varchar (60)
,TypeDelivery varchar (25)
,GestationalAge varchar (25)
,EDC CHAR (10)
,BabyDob CHAR(10)
,FacilName varchar(60)
,RateCode varchar(25)
,Plans varchar(25))

INSERT INTO #NewBaby2

Select NB1.MomFullName
,NB1.MomID
,NB1.MomMemID
,NB1.MomSubID
,NB1.BabyGender
,NB1.BabyWeight
,NB1.BabyWeightCat
,NB1.TypeDelivery
,NB1.GestationalAge
,NB1.EDC
,NB1.BabyDob
,NB1.FacilName
,NB1.RateCode
,NB1.Plans

From #NewBaby1 NB1

SELECT NB2.MomFullName
,NB2.MomID
,NB2.MomMemID
,MAX(NB2.MomSubID) as MomSubID
,MAX(NB2.BabyGender) as BabyGender
,MAX(NB2.BabyDob) as BabyDob
,MAX(NB2.EDC) as EDC
,MAX(NB2.BabyWeight) as BabyWeight
,MAX(NB2.BabyWeightCat) as BabyWeightCat
,MAX(NB2.TypeDelivery) as TypeDelivery
,MAX(NB2.GestationalAge) as GestationalAge
,NB2.FacilName
,MAX(NB2.RateCode) as RateCode
,MAX(NB2.Plans) as Plans

FROM #NewBaby2 AS NB2

GROUP BY NB2.MomFullName
,NB2.MomID
,NB2.FacilName
,NB2.MomMemID
---------------------------------------------------------------
I need to join mom's info (from above query) with babies info (new tables) and bring Babies IDs to Mom's info where they are available. Thus, if there are 100 records for mom, there may be only 20 Babies IDs. The second query (below) picks only 26 with many fields as NULL. What I want is to get the 100 records and populate the 20 IDs leaving the 80 records with blanks. Here is the second query:

----------------------------------------------Adding Babies IDs--------------------------------------

/**********************************************************************************/
if object_id('tempdb..#NewBaby3') is not null begin drop table #NewBaby3 end
/**********************************************************************************/

Select DISTINCT
NB2.Momfullname
,NB2.MomSubId
,NB2.MomMemID
,NB2.MomID
,eBaby.carriermemid as BabyID
--,ISNULL(eBaby.carriermemid, 'MissingID') as BabyID
,NB2.BabyGender as BabyGender
,NB2.BabyDob
, NB2.EDC
, NB2.BabyWeight
, NB2.BabyWeightCat
, NB2.TypeDelivery
, NB2.GestationalAge
,NB2.FacilName
, NB2.RateCode
, NB2.Plans

INTo #NewBaby3
from #NewBaby2 NB2
left join memberview mBaby (nolock) on mBaby.headofhouse = NB2.MomSubID
and mBaby.memid <> NB2.MomMemID
join enrollkeys eBaby (nolock) on eBaby.memid = mBaby.memid

where (mBaby.dob >= '05/1/2010')
Group by NB2.Momfullname
,NB2.MomSubId
,NB2.MomMemID
,NB2.MomID
,eBaby.carriermemid as BabyID
--,ISNULL(eBaby.carriermemid, 'MissingID') as BabyID
,NB2.BabyGender as BabyGender
,NB2.BabyDob
, NB2.EDC
, NB2.BabyWeight
, NB2.BabyWeightCat
, NB2.TypeDelivery
, NB2.GestationalAge
,NB2.FacilName
, NB2.RateCode
, NB2.Plans

Select * from #NewBaby3

I hope my explanation make sense!

HM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-25 : 12:31:17

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources