Nulls when pivoting - SQL Server Forums

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

Nulls when pivoting

Author

Topic

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-29 : 13:56:24

Hi,

I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.

Your insight is greatly appreciated.

select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000
from (
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'
Group by
BusinessUnitCode,
EffectiveCCYYMM

) m
inner join (
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions
WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factadmissions.BusinessUnitCode IS not Null
Group by
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM

) a
on m.BusinessUnitCode = a.BusinessUnitCode
and m.EffectiveCCYYMM = a.AdmitCCYYMM
) d
pivot
(
sum(AdmitsPer1000)
for BusinessUnitCode in ([EP],[HF],[VN])
) piv
order by 1,2,3;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:14:14

NULLS are because you dont have data for all Unit Codes in all cases. if you want to avoid them you need to use ISNULL in final select like


select Date,ISNULL(EP,0) AS EP,ISNULL(HF,0) AS HF,ISNULL(VN,0) AS VN
from
(
select 
a.BusinessUnitCode, 
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000
from (
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership 
Where EffectiveCCYYMM >= '200701'
Group by
BusinessUnitCode,
EffectiveCCYYMM

) m
inner join (
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits] 
FROM factAdmissions
WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factadmissions.BusinessUnitCode IS not Null
Group by
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM

) a
on m.BusinessUnitCode = a.BusinessUnitCode
and m.EffectiveCCYYMM = a.AdmitCCYYMM
) d
pivot
(
sum(AdmitsPer1000)
for BusinessUnitCode in ([EP],[HF],[VN])
) piv
order by 1,2,3;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-30 : 09:26:02

Thank you for the help on this. This forum is great! The short time I've been here, I have learned a lot reading through the different posts and asking questions. Thanks again.

quote:

Originally posted by visakh16

NULLS are because you dont have data for all Unit Codes in all cases. if you want to avoid them you need to use ISNULL in final select like


select Date,ISNULL(EP,0) AS EP,ISNULL(HF,0) AS HF,ISNULL(VN,0) AS VN
from
(
select 
a.BusinessUnitCode, 
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000
from (
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership 
Where EffectiveCCYYMM >= '200701'
Group by
BusinessUnitCode,
EffectiveCCYYMM

) m
inner join (
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits] 
FROM factAdmissions
WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factadmissions.BusinessUnitCode IS not Null
Group by
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM

) a
on m.BusinessUnitCode = a.BusinessUnitCode
and m.EffectiveCCYYMM = a.AdmitCCYYMM
) d
pivot
(
sum(AdmitsPer1000)
for BusinessUnitCode in ([EP],[HF],[VN])
) piv
order by 1,2,3;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 04:44:55

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources