Calculating Admits Per 1000 - 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

Calculating Admits Per 1000

Author

Topic

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-27 : 11:02:00

Hi guys!

I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.

Below are my queries from the two datasets.

MemberMonths dataset:

Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'
Group by
BusinessUnitCode,
EffectiveCCYYMM

Order by 1 Desc;

Admits dataset:

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
Order by 1;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-27 : 12:13:30

[code]select m.BusinessCodeUnit, m.EffectiveCCYYMM, a.[Count of Admits] / 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 = EffectiveCCYYMM[/code]

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-27 : 12:46:24

Thank you for replying. I tried your code, but the dataset returned all zeros for the AdmitsPer1000 field.

quote:

Originally posted by Bustaz Kool

select m.BusinessCodeUnit, m.EffectiveCCYYMM, a.[Count of Admits] / 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 = EffectiveCCYYMM

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-27 : 15:36:44

From a quick search through the threads, I found out that I needed to add *1.0 before /. Now the correct result is returned from the code.

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-27 : 18:28:50

Glad you found an answer...

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-08-27 : 22:03:36

you may directly add .0 to 12000

quote:

Originally posted by Briceston

Thank you for replying. I tried your code, but the dataset returned all zeros for the AdmitsPer1000 field.

quote:

Originally posted by Bustaz Kool

select m.BusinessCodeUnit, m.EffectiveCCYYMM, a.[Count of Admits] / m.MemberCount * 12000.0 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 = EffectiveCCYYMM

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-29 : 13:47:29

Thanks for the for the further insight Waterduck.

quote:

Originally posted by waterduck

you may directly add .0 to 12000

quote:

Originally posted by Briceston

Thank you for replying. I tried your code, but the dataset returned all zeros for the AdmitsPer1000 field.

quote:

Originally posted by Bustaz Kool

select m.BusinessCodeUnit, m.EffectiveCCYYMM, a.[Count of Admits] / m.MemberCount * 12000.0 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 = EffectiveCCYYMM

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources