SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get rid of Nulls and keep the original rec?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HelalM
Starting Member

USA
19 Posts

Posted - 08/25/2010 :  12:10:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 08/25/2010 :  12:31:17  Show Profile  Reply with Quote
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000