Link to home
Avatar of Mike Eghtebas
Mike EghtebasFlag 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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

what version of SQL Server are you using?
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
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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mike Eghtebas

ASKER

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

Regards,

Mike