|
HelalM
Starting Member
USA
19 Posts |
 Posted - 08/25/2010 : 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 |
|
How to get rid of Nulls and keep the original rec?
