none
Too Many Rows In Select Query RRS feed

  • Question

  • I copied this query from MS Access and was able to clean it up in SQL. The table tblFactReferrals has multiple records for each patient. Each patient usually has about 4 records with the same referral date. I only need one record from each. This shows all of them. The field table tblFactReferrals.ReferralStatus has the word Initiated in one record. The others do not. Can you advise on this?

    BEGINNING OF CODE

    SELECT        MIN(tblFactReferrals.ReferralDate) AS MinOfReferralDate, tblDimPatients.LastContactDate, tblDimPatients.PatientID, tblDimPatients.LastName,
                             tblDimPatients.FirstName, tblDimPatients.MInitial, tblDimPatients.Suffix, tblDimPatients.MedicalRecordNumber, tblDimPatients.GraduatingYear
    FROM            tblDimPatients INNER JOIN
                             tblFactReferrals ON tblDimPatients.PatientID = tblFactReferrals.PatientID
    GROUP BY tblDimPatients.LastContactDate, tblDimPatients.PatientID, tblDimPatients.LastName, tblDimPatients.FirstName, tblDimPatients.MInitial, tblDimPatients.Suffix,
                             tblDimPatients.MedicalRecordNumber, tblDimPatients.GraduatingYear, tblDimPatients.Status
    HAVING        (tblDimPatients.Status = 'Referral')
    ORDER BY MinOfReferralDate DESC, tblDimPatients.LastName, tblDimPatients.FirstName, tblDimPatients.MInitial, tblDimPatients.Suffix

    END OF CODE

    Thank you.

    Monday, August 12, 2019 11:38 AM

Answers

  • (...) Each patient usually has about 4 records with the same referral date. I only need one record from each. This shows all of them. The field table tblFactReferrals.ReferralStatus has the word Initiated in one record. The others do not.

    And what is the rule for choosing which record should be displayed per patient/date?

    If the rule is to list only the rows that the ReferralStatus column contains the word "Initiated", here's a possibility:

    -- code #1
    SELECT F.ReferralDate, P.LastContactDate, P.PatientID, P.LastName,
           P.FirstName, P.MInitial, P.Suffix, P.MedicalRecordNumber, P.GraduatingYear
    
      from tblDimPatients as P
           inner join tblFactReferrals as F on P.PatientID = F.PatientID
    
      where P.Status = 'Referral'
            and F.ReferralStatus = 'Initiated';
     



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Monday, August 12, 2019 3:12 PM
    Answerer

All replies

  • Please post DDLs and INSERT INTO … statements with some sample data. Without DDLs and sample data, I just guess the problem is the column "tblDimPatients.LastContactDate" which has the different values. You may remove tblDimPatients.LastContactDate from the GROUP BY statement and use MAX(tblDimPatients.LastContactDate) instead of tblDimPatients.LastContactDate in the SELECT statement.

    A Fan of SSIS, SSRS and SSAS

    Monday, August 12, 2019 1:39 PM
    Answerer
  • (...) Each patient usually has about 4 records with the same referral date. I only need one record from each. This shows all of them. The field table tblFactReferrals.ReferralStatus has the word Initiated in one record. The others do not.

    And what is the rule for choosing which record should be displayed per patient/date?

    If the rule is to list only the rows that the ReferralStatus column contains the word "Initiated", here's a possibility:

    -- code #1
    SELECT F.ReferralDate, P.LastContactDate, P.PatientID, P.LastName,
           P.FirstName, P.MInitial, P.Suffix, P.MedicalRecordNumber, P.GraduatingYear
    
      from tblDimPatients as P
           inner join tblFactReferrals as F on P.PatientID = F.PatientID
    
      where P.Status = 'Referral'
            and F.ReferralStatus = 'Initiated';
     



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Monday, August 12, 2019 3:12 PM
    Answerer