asked on

Find % in query

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

PortletPaul

what version of SQL Server are you using?

Brian Crowe

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

PortletPaul

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)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

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

Regards,

Mike