Avatar of Mike Eghtebas
Mike Eghtebas
Flag for United States of America 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Mike Eghtebas

8/22/2022 - Mon
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)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mike Eghtebas

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

Regards,

Mike