Loading ...
Sorry, an error occurred while loading the content.

Brutal query - plz help

Expand Messages
  • delaney.brian@ymail.com
    I need to modify the following query so it does the following: 1. Loop through tables Aanswers - Qanswers 2. for each table if one of the Q*Radios = 3 get the
    Message 1 of 1 , Sep 13, 2011
    • 0 Attachment
      I need to modify the following query so it does the following:

      1. Loop through tables Aanswers - Qanswers
      2. for each table if one of the Q*Radios = 3 get the section name and a count of how many.

      3. display the final results:
      Name of grouphome, secion name, number of '3' s in that section.

      The problem is the answers tables have duplicate records for one audit. So we have the datemodified field. The following query works, but only returns the record of the max(datemodied) and ignores the other audits.

      It is like I should be grouping on audit ids and max date?

      Here you go-----------------------------------------------------:

      -- sp_NonComplianceAlertsList 2
      CREATE PROC [dbo].[sp_NonComplianceAlertsList]
      (
      @flag INT
      )
      AS
      BEGIN

      IF OBJECT_ID('tempdb..#GroupHome') IS NOT NULL
      BEGIN
      DROP TABLE #GroupHome
      END

      BEGIN TRAN
      SET NOCOUNT ON

      CREATE TABLE #GroupHome
      (
      DateModified datetime,
      GroupHomeID VARCHAR(40),
      GroupHomeName VARCHAR(100),
      AuditID VARCHAR(40),
      SectionName VARCHAR(100),
      AuditCount INT
      )

      CREATE Table #ATemp
      (
      AuditID VARCHAR(40),
      DateModified datetime
      )

      INSERT INTO #ATemp (AuditID, DateModified)
      SELECT
      AuditID,max(A.DateModified)
      FROM
      sqlprod.PA.dbo.AAnswers A
      GROUP BY AuditID

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      AA.DateModified, GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      #ATemp A
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = A.AuditID
      INNER JOIN sqlprod.PA.dbo.AAnswers AA ON AA.DateModified = A.DateModified
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3 OR Q8radio = 3 OR Q9radio=3 OR Q10radio=3 OR
      Q11radio=3 OR Q12radio = 3 OR Q13radio=3 OR Q14radio = 3 OR Q15radio=3 OR Q16radio = 3 OR Q17radio=3 OR Q18radio = 3 OR Q19radio=3 OR Q20radio=3 OR
      Q21radio=3 OR Q22radio = 3 OR Q23radio=3 OR Q24radio = 3 OR Q25radio=3 OR Q26radio = 3 OR Q27radio=3 OR Q28radio = 3 OR Q29radio=3 OR Q30radio=3)
      AND AD.ClosedAudit = 0 AND AA.DateModified = A.DateModified
      GROUP BY GH.GHID, GH.GHName, AD.AuditID, AA.DateModified
      UPDATE #GroupHome SET SectionName = 'General' WHERE SectionName IS NULL
      DROP TABLE #ATemp

      --T
      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(B.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.BAnswers B
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = B.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3 OR Q8radio = 3 OR Q9radio=3)
      AND AD.ClosedAudit = 0 AND B.DateModified = (SELECT max(DateModified) FROM BAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Bathrooms' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(C.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.CAnswers C
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = C.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3 OR Q8radio = 3 OR Q9radio=3)
      AND AD.ClosedAudit = 0 AND C.DateModified = (SELECT max(DateModified) FROM CAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Kitchen' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(D.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.DAnswers D
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = D.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3)
      AND AD.ClosedAudit = 0 AND D.DateModified = (SELECT max(DateModified) FROM DAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Laundry' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(E.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.EAnswers E
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = E.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3)
      AND AD.ClosedAudit = 0 AND E.DateModified = (SELECT max(DateModified) FROM EAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Stairwells' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(F.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.FAnswers F
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = F.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3)
      AND AD.ClosedAudit = 0 AND F.DateModified = (SELECT max(DateModified) FROM FAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Doors' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(G.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.GAnswers G
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = G.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3) AND G.DateModified = (SELECT max(DateModified) FROM GAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Exits' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(H.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.HAnswers H
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = H.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3 OR Q8radio = 3 OR Q9radio=3)
      AND AD.ClosedAudit = 0 AND H.DateModified = (SELECT max(DateModified) FROM HAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Extinguishing Devices' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(I.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.IAnswers I
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = I.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3 OR Q8radio = 3 OR Q9radio=3 OR Q10radio=3 OR
      Q11radio=3 OR Q12radio = 3 OR Q13radio=3 OR Q14radio = 3 OR Q15radio=3 OR Q16radio =3)
      AND AD.ClosedAudit = 0 AND I.DateModified = (SELECT max(DateModified) FROM IAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Smoke Detector System' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(J.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.JAnswers J
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = J.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3) AND J.DateModified = (SELECT max(DateModified) FROM JAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Emergency Protection Plan' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(K.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.KAnswers K
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = K.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 )
      AND AD.ClosedAudit = 0 AND K.DateModified = (SELECT max(DateModified) FROM KAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Fire Drills' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(L.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.LAnswers L
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = L.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3)
      AND AD.ClosedAudit = 0 AND L.DateModified = (SELECT max(DateModified) FROM LAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Tornado Drills' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(M.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.MAnswers M
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = M.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3)
      AND AD.ClosedAudit = 0 AND M.DateModified = (SELECT max(DateModified) FROM MAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Evacuation Assessment' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(N.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.NAnswers N
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = N.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3)
      AND AD.ClosedAudit = 0 AND N.DateModified = (SELECT max(DateModified) FROM NAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID

      UPDATE #GroupHome SET SectionName = 'Smoking Policy' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(O.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.OAnswers O
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = O.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3)
      AND AD.ClosedAudit = 0 AND O.DateModified = (SELECT max(DateModified) FROM OAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Lawn Maintenance' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(P.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.PAnswers P
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = P.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3)
      AND AD.ClosedAudit = 0 AND P.DateModified = (SELECT max(DateModified) FROM PAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Emergency Preparedness' WHERE SectionName IS NULL

      INSERT INTO #GroupHome (DateModified, GroupHomeID, GroupHomeName, AuditID, AuditCount)
      SELECT
      max(Q.DateModified), GH.GHID, GH.GHName, AD.AuditID, COUNT(*)
      FROM
      sqlprod.PA.dbo.QAnswers Q
      INNER JOIN sqlprod.PA.dbo.GHAudit AD ON AD.AuditID = Q.AuditID
      INNER JOIN sqlprod.Providers.dbo.CMHGroupHomes GH ON GH.GHID = AD.GHID
      WHERE
      (Q1radio=3 OR Q2radio = 3 OR Q3radio=3 OR Q4radio = 3 OR Q5radio=3 OR Q6radio = 3 OR Q7radio=3)
      AND AD.ClosedAudit = 0 AND Q.DateModified = (SELECT max(DateModified) FROM QAnswers)
      GROUP BY GH.GHID, GH.GHName, AD.AuditID
      UPDATE #GroupHome SET SectionName = 'Monthly Checks Maintained' WHERE SectionName IS NULL

      ----SELECT * FROM #GroupHome

      IF @flag = 1
      BEGIN
      SELECT TOP 5 GroupHomeID, GroupHomeName, Count(*) AS [Count] FROM #GroupHome
      GROUP BY GroupHomeID, GroupHomeName, DateModified
      ORDER BY [Count] Desc
      END
      ELSE IF @flag = 2
      BEGIN
      SELECT GroupHomeName, GroupHomeID, AuditID, SectionName, TI.TableName
      FROM #GroupHome GH
      INNER JOIN dbo.PATableIndex TI ON TI.HeadingName = GH.SectionName
      GROUP BY AuditID, SectionName, GroupHomeName, GroupHomeID, TI.TableName
      ORDER BY GroupHomeName, SectionName
      END
      DROP TABLE #GroupHome

      SET NOCOUNT OFF
      COMMIT TRAN

      END
      GO
    Your message has been successfully submitted and would be delivered to recipients shortly.