Find % in query

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
The attached image shows the following query with data:
SELECT m.YYYY, m.LastYearCount, m.CurrYearCount, m.[01], m.[02], m.[03], m.[04], m.[05], m.[06], m.[07], m.[08], m.[09], m.[10], m.[11], m.[12], m.MedCtr_ID, m.StatType_ID
FROM tMonthlyMedCenter m
WHERE (((m.YYYY)=fnYYYY()) AND ((m.MedCtr_ID)=2) AND ((m.StatType_ID)=14 Or (m.StatType_ID)=15));

Open in new window


Currently, there are two rows filtered by StatType 14, and 15. This query needs to find % for each column and turn it to a single row showing % values from [01], [02],...[12] using:

Percent_01: ([01] StatType15) / ([01] StatType14)

There StatTypes other than 14 and 15. In some other question, I will expand the scope to see how far it could go.

Question: How can this calculation could be done with a single query?

Because performance is of a concern, I am open to another way of doing this also (for instance storing some of the rows in tem table or using VBA to do it).

Thank you.
Query1.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
what version of SQL Server are you using?
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
If you're using 2012 then LAG/LEAD windowing functions would make this much easier but...

SELECT m15.YYYY, m15.LastYearCount, m15.CurrYearCount,
      m15.[01] / m14.[01] AS Percent_01,
      m15.[02] / m14.[02] AS Percent_02,
      m15.[03] / m14.[03] AS Percent_03,
      m15.[04] / m14.[04] AS Percent_04,
      m15.[05] / m14.[05] AS Percent_05,
      m15.[06] / m14.[06] AS Percent_06,
      m15.[07] / m14.[07] AS Percent_07,
      m15.[08] / m14.[08] AS Percent_08,
      m15.[09] / m14.[09] AS Percent_09,
      m15.[10] / m14.[10] AS Percent_10,
      m15.[11] / m14.[11] AS Percent_11,
      m15.[12] / m14.[12] AS Percent_12
FROM tMonthlyMedCenter AS m14
INNER JOIN tMonthlyMedCenter AS m15
      ON m14.YYYY = m5.YYYY
      AND m14.StatType_ID = 14
      AND m15.StatType_ID = 15
WHERE m14.YYYY=fnYYYY()
      AND m.MedCtr_ID = 2
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
are there normalized tables before you reach "tMonthlyMedCenter"?
i.e.
is "tMonthlyMedCenter" a temporary table that is built from base tables?

It may be more efficient to make the % calculations earlier, but here is a way.
SELECT
      tmm15.YYYY
    , tmm15.LastYearCount
    , tmm15.CurrYearCount
    , tmm15.[01] / (tmm14.[01] * 1.0) as [pct01]
    , tmm15.[02] / (tmm14.[02] * 1.0) as [pct02]
    , tmm15.[03] / (tmm14.[03] * 1.0) as [pct03]
    , tmm15.[04] / (tmm14.[04] * 1.0) as [pct04]
    , tmm15.[05] / (tmm14.[05] * 1.0) as [pct05]
    , tmm15.[06] / (tmm14.[06] * 1.0) as [pct06]
    , tmm15.[07] / (tmm14.[07] * 1.0) as [pct07]
    , tmm15.[08] / (tmm14.[08] * 1.0) as [pct08]
    , tmm15.[09] / (tmm14.[09] * 1.0) as [pct09]
    , tmm15.[10] / (tmm14.[10] * 1.0) as [pct10]
    , tmm15.[11] / (tmm14.[11] * 1.0) as [pct11]
    , tmm15.[12] / (tmm14.[12] * 1.0) as [pct12]
    , tmm15.MedCtr_ID
    , tmm15.StatType_ID
FROM tMonthlyMedCenter AS tmm15
INNER JOIN (
    SELECT
    *
    FROM tMonthlyMedCenter
    WHERE StatType_ID = 14
  ) AS tmm14 ON tmm15.YYYY = tmm14.YYYY AND tmm15.MedCtr_ID = tmm14.MedCtr_ID
WHERE tmm15.StatType_ID = 15


CREATE TABLE tMonthlyMedCenter
	([YYYY] int, [01] int, [02] int, [03] int, [04] int, [05] int, [06] int, [07] int, [08] int, [09] int, [10] int, [11] int, [12] int, [MedCtr_ID] int, [StatType_ID] int, LastYearCount int, CurrYearCount int)
;
	

Open in new window

please note it would help in future if you provided sample data in a reusable format (not images)
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
sorry, this may be the preferred percentage calculation. If you only want an integer result use 100 instead of 100.0
SELECT
      tmm15.YYYY
    , tmm15.LastYearCount
    , tmm15.CurrYearCount
    , (tmm15.[01] * 100.0) / tmm14.[01] as [pct01]
    , (tmm15.[02] * 100.0) / tmm14.[02] as [pct02]
    , (tmm15.[03] * 100.0) / tmm14.[03] as [pct03]
    , (tmm15.[04] * 100.0) / tmm14.[04] as [pct04]
    , (tmm15.[05] * 100.0) / tmm14.[05] as [pct05]
    , (tmm15.[06] * 100.0) / tmm14.[06] as [pct06]
    , (tmm15.[07] * 100.0) / tmm14.[07] as [pct07]
    , (tmm15.[08] * 100.0) / tmm14.[08] as [pct08]
    , (tmm15.[09] * 100.0) / tmm14.[09] as [pct09]
    , (tmm15.[10] * 100.0) / tmm14.[10] as [pct10]
    , (tmm15.[11] * 100.0) / tmm14.[11] as [pct11]
    , (tmm15.[12] * 100.0) / tmm14.[12] as [pct12]
    , tmm15.MedCtr_ID
    , tmm15.StatType_ID
FROM tMonthlyMedCenter AS tmm15
INNER JOIN (
    SELECT
    *
    FROM tMonthlyMedCenter
    WHERE StatType_ID = 14
  ) AS tmm14 ON tmm15.YYYY = tmm14.YYYY AND tmm15.MedCtr_ID = tmm14.MedCtr_ID
WHERE tmm15.StatType_ID = 15

Open in new window

Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank you very much. My apology for not being able to respond to your questions.

Regards,

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial