locked
Helping modifying select statement RRS feed

  • Question

  • Below I have a stored procedure that returns the EmployerName, EmployerID and the date of the most recent report (in the "reports" table) for each employer listed in the "employers" table.

    However, I want to modify this stored procedure in two ways and don't know how.

    1. I want to modify this proceudre so that all employers in the employers table are included in the returned results and if there is no matching report for a given employer, then the string "No Match Found" is returned in the third column of the table, in place of the minimum date.

    2. For the third column, instead of just returning the most recent report that matches the employerID and reportPeriod, I also want the report to have a r.ReportType value equal to 'Report Type A.' I would then like to have a fourth column that has the most recent report that
    matches the employerID, the reportPeriod and has a r.ReportType value equal to 'Report Type B.'

    Any suggestions are much appreciated.


    Code Snippet

    CREATE PROCEDURE GetMonthlyReport
    @ReportPeriod varchar(20)
    AS

    SELECT e.EmployerID,e.EmployerName,MIN(r.DateReceived) AS MostRecentReport
    FROM employers e
    INNER JOIN reports r
    ON r.EmployerID=e.EmployerID
    WHERE r.ReportPeriod=@ReportPeriod
    GROUP BY e.EmployerID,e.EmployerName
    Friday, July 18, 2008 1:56 PM

All replies

  • Thanks for the suggestion.

    Unfortunately, even with the left join, a few employers from the employers table are not in the returned results. Specifically the employers whose EmployerIDs are not listed as the EmployerID for any rows in reports table are not included in the results.
    Friday, July 18, 2008 3:00 PM
  • This is because

     

    WHERE r.ReportPeriod=@ReportPeriod

     

    needs to be

     

    AND r.ReportPeriod=@ReportPeriod

     

    if you use the left joined table in a WHERE clause you are basically creating an INNER JOIN

     

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks
    Friday, July 18, 2008 3:20 PM
  • Great, thanks!

    All employers in the employers table are now included in the returned results!

    If anyone has an suggestions for how to create new columns in the returned results table for specific types of reports (as described in my original post), that would be much appreciated!
    Friday, July 18, 2008 3:39 PM
  • Thanks for the tip, I'm a SQL novice and hadn't used derived tables before.

    I have played around with it and was able to use a derived table to specify the type of report that I want in the third column of my returned results:

    Code Snippet

    CREATE PROCEDURE GetMonthlySummary2
    @ReportPeriod varchar(20)
    AS

    SELECT e.EmployerID,e.EmployerName, MAX(r1.DateReceived) AS ReportA
    FROM employers e
    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type A') As r1

    ON r1.EmployerID=e.EmployerID
    AND r1.ReportPeriod=@ReportPeriod
    GROUP BY e.EmployerID,e.EmployerName


    However, I'm really stumped about how to create a fourth column that would contain the same results for a different type of report.


    Friday, July 18, 2008 5:08 PM
  • It's going slow. I've spent a long time playing around with this and I'm really stuck.

    I fell like the stored procedure should look something like this, but I'm getting an error that says:

    Error -2147217900
     

    Incorrect syntax near the keyword 'ON'.



    HELP!

    Code Snippet

    CREATE PROCEDURE GetMonthlySummary
    @ReportPeriod varchar(20)
    AS

    SELECT e.EmployerID,e.EmployerName, MAX(q.DateReceived) AS QR, MAX(d.DateReceived) AS Dues

    FROM employers e

    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type Q')

    ON q.EmployerID=e.EmployerID
    AND q.ReportPeriod=@ReportPeriod
    GROUP BY e.EmployerID
    ) q

    JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type D') As d

    ON q.EmployerID=e.EmployerID AND q.ReportPeriod=@ReportPeriod
    GROUP BY e.EmployerID,e.EmployerName


    Monday, July 21, 2008 1:34 PM
  • Thanks for the suggestion!

    UInfortuantely when I try that syntax, I get the following error:

    Error -2147217900
     

    The multi-part identifier "e.EmployerID" could not be bound.



    Also, I'm not sure where the "delta" came from on the second to last line in the code you suggest. I figured that you just meant d.EmployerID and d.ReportPeriod (instead of delta.EmployerID and delta.ReportPeriod), although I still get the same error even if I make those changes.

    Any idea what is causing the error?
    Monday, July 21, 2008 5:52 PM
  • I am still receiving the same error, even with the new syntax you suggested.

    To clarify my intentions:

    I am trying to write a stored procedure that returns a table with four columns:

    Col 1 - A list of all the EmployerID values in the employers table (EmployerID is the primary key for the employers table).
    Col 2 - The EmployerName value from the employers table that matches the EmployerID in Col 1
    Col 3 - The most recent DateReceived value (if one exists) from the reports table where:
    a. reports.EmployerID matches the EmployerID in Col 1 of the returned results;
    b. reports.ReportType matches 'Report Type Q'; and
    c. reports.ReportPeriod value is equal to the @ReportPeriod input parameter.
    Col 4 - Same as Col 3 except the reports.ReportType value needs to match 'Report Type D'

    Thanks for continuing to help me solve this tricky problem.
    Monday, July 21, 2008 7:47 PM
  • The tables are actually created by data entered over time through a vb.net website, and some of the data is sensitive so I can't really post it, but I have included the structure of both tables below.

    There are approximately 25 employers in the employers table and a few thousand reports in the reports table.

    For each EmployerID there is anywhere from 0-5 reports for any given pair of ReportType and ReportPeriod, hence the use of LEFT JOIN to make sure that all the employers are included in the returned results.

    If this isn't enough information and you need me to post CREATE TABLES with sample data, I can.

    Thanks.


    Employers Table


    Key Column Type Length Prec. Scale Nullable Default Rule Id. Id. start Id. seed Row GUID  
    Y EmployerID int 4  10  0  False      True  1  1  False     
      EmployerName varchar 50      True      False      False     
      Address1 varchar 50      True      False      False     
      Address2 varchar 50      True      False      False     
      City varchar 50      True      False      False     
      State varchar 2      True      False      False     
      Zip varchar 5      True      False      False     

    Reports Table

    Key Column Type Length Prec. Scale Nullable Default Rule Id. Id. start Id. seed Row GUID  
    Y ReportID int 4  10  0  False      True  1  1  False     
      EmployerID int 4  10  0  False      False      False     
      ReportType varchar 50      False      False      False     
      DateReceived datetime 8      False      False      False     
      ReportPeriod varchar 20      False      False      False     
      ReportMedia varchar 50      True      False      False     
      Notes varchar 1000      True      False      False     


















































































































    Monday, July 21, 2008 8:59 PM
  • Sorry, that got pasted in kinda garbled.

    I have included CREATE TABLES with sample data below.

    With that sample data, here is the results I am looking for:

    Employer ID          Employer Name          QR               Dues
    1000                      Employer A                   7/1/2008       7/1/2008
    1001                      Employer B                   7/1/2008
    1002                      Employer C                                       7/1/2008



    CREATE TABLE Employers
    (
      EmployerID int PRIMARY KEY,
      EmployerName varchar(50)
    )

    INSERT INTO Employers (EmployerID, EmployerName)
    VALUES ('1000', 'Employer A')

    INSERT INTO Employers (EmployerID, EmployerName)
    VALUES ('1001', 'Employer B')

    INSERT INTO Employers (EmployerID, EmployerName)
    VALUES ('1002', 'Employer C')


    CREATE TABLE Reports
    (
      ReportID int IDENTITY(1,1) PRIMARY KEY,
      EmployerID int,
      ReportType varchar(50),
      DateReceived datetime,
      ReportPeriod varchar(20)
    )

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)
    VALUES ('1000','Report Type Q','7/1/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)
    VALUES ('1000','Report Type D','7/1/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)
    VALUES ('1001','Report Type Q','7/1/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)
    VALUES ('1002','Report Type D','7/1/2008','June / Q2 2008')

    Tuesday, July 22, 2008 2:41 PM
  • I assembled into a working set. Can you comment on it?

     

    Code Snippet

    use tempdb;

    CREATE TABLE Employers

    (

    EmployerID int PRIMARY KEY,

    EmployerName varchar(50)

    )

    INSERT INTO Employers (EmployerID, EmployerName)

    VALUES ('1000', 'Employer A')

    INSERT INTO Employers (EmployerID, EmployerName)

    VALUES ('1001', 'Employer B')

    INSERT INTO Employers (EmployerID, EmployerName)

    VALUES ('1002', 'Employer C')

    -- drop table Reports

    CREATE TABLE Reports

    (

    ReportID int IDENTITY(1,1) PRIMARY KEY,

    EmployerID int,

    ReportType varchar(50),

    DateReceived datetime,

    ReportPeriod varchar(20)

    )

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)

    VALUES ('1000','Report Type Q','7/1/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)

    VALUES ('1000','Report Type D','7/2/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)

    VALUES ('1001','Report Type Q','7/1/2008','June / Q2 2008')

    INSERT INTO Reports (EmployerID,ReportType,DateReceived,ReportPeriod)

    VALUES ('1002','Report Type D','7/2/2008','June / Q2 2008')

    GO

    CREATE PROCEDURE GetMonthlySummary

    @ReportPeriod varchar(20)

    AS

    SELECT e.EmployerID,e.EmployerName, MAX(q.DateReceived) AS QR, MAX(d.DateReceived) AS Dues

    -- select *

    FROM employers e

    LEFT JOIN

     

    (Select * FROM reports WHERE reports.ReportType='Report Type Q'

    ) q

    ON q.EmployerID=e.EmployerID

    AND q.ReportPeriod=@ReportPeriod

    JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type D') As d

    ON q.EmployerID=e.EmployerID AND q.ReportPeriod=@ReportPeriod

    GROUP BY e.EmployerID,e.EmployerName

    GO

    exec GetMonthlySummary 'June / Q2 2008'

    GO

     

     

    Thursday, July 24, 2008 7:41 AM
  • When I execute the code provided in your last post, I get the following results:

    Employer ID     Employer Name       QR                                   Dues
    1000                Employer A             2008-07-01 00:00:00.000    2008-07-02 00:00:00.000
    1001                Employer B             2008-07-01 00:00:00.000    2008-07-02 00:00:00.000

    However, the desired results are:

    Employer ID     Employer Name       QR                                   Dues
    1000                Employer A             2008-07-01 00:00:00.000    2008-07-02 00:00:00.000
    1001                Employer B             2008-07-01 00:00:00.000   
    1002                Employer C                                                     2008-07-01 00:00:00.000

    Any ideas? Thanks!
    Monday, August 4, 2008 6:32 PM
  • Hi,

    While I fully appreciate your "teach a man to fish" approach to offering advice, I assure you that have tried many different "final modifications" and am legitimately stuck, not fishing for answers.

    Here is why I am stuck:

    If we forget about the fouth colum for a minute and are just interested in reports where report type is equal to 'Report Type Q', then we can use the following abridged version of the stored proc we've been discussing, which generates these (correct) results:
                             

     

    Code Snippet

    ALTER PROCEDURE [dbo].[GetMonthlySummary]

    @ReportPeriod varchar(20)

    AS

    SELECT e.EmployerID,e.EmployerName, MAX(q.DateReceived) AS QR

    FROM Employers e

    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type Q'

    ) q

    ON q.EmployerID=e.EmployerID

    AND q.ReportPeriod=@ReportPeriod

    GROUP BY e.EmployerID,e.EmployerName

     

     

    Employer ID     Employer Name       QR                                  

    1000                Employer A             2008-07-01 00:00:00.000   

    1001                Employer B             2008-07-01 00:00:00.000   
    1002                Employer C             NULL              

     

    So, it seems to me that the get the final results I am looking for, I need to start with that table returned by the abridged stored proc and then do another left join with a table that includes the subset of the reports table where ReportType = 'Report Type D'. Accordingly, the only "final modification" I can think to make to the last version of the stored proc that you suggested is to repalce with the second join with a left join, as show below.

     

    Code Snippet

    ALTER PROCEDURE [dbo].[GetMonthlySummary]

    @ReportPeriod varchar(20)

    AS

    SELECT e.EmployerID,e.EmployerName, MAX(q.DateReceived) AS QR, MAX(d.DateReceived) AS Dues

    FROM Employers e

    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type Q'

    ) q

    ON q.EmployerID=e.EmployerID

    AND q.ReportPeriod=@ReportPeriod

    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type D') As d

    ON q.EmployerID=e.EmployerID AND q.ReportPeriod=@ReportPeriod

    GROUP BY e.EmployerID,e.EmployerName

     

     

    However, that generates these (incorrect) results:

     

    EmployerID  EmployerName  QR                                Dues

    1000            Employer A       2008-07-01 00:00:00.000 2008-07-02 00:00:00.000
    1001            Employer B       2008-07-01 00:00:00.000 2008-07-02 00:00:00.000
    1002            Employer C       NULL                             NULL

     

    If you know what needs to be done to fix this stored proc, I kindly request that you share that knowledge with me, and if you are so inclined, explain why the last two version of the stored proc (the last one you suggested and my "final modification") don't work as think they should.

     

    Thank you.

    Monday, August 4, 2008 7:58 PM
  • For the record, the correct code is:

    CREATE PROCEDURE GetMonthlySummary

    @ReportPeriod varchar(20)

    AS

    SELECT e.EmployerID,e.EmployerName, MAX(q.DateReceived) AS QR, MAX(d.DateReceived) AS Dues

    -- select *

    FROM employers e

    LEFT JOIN

     

    (Select * FROM reports WHERE reports.ReportType='Report Type Q'

    ) q

    ON q.EmployerID=e.EmployerID

    AND q.ReportPeriod=@ReportPeriod

    LEFT JOIN

    (Select * FROM reports WHERE reports.ReportType='Report Type D') As d

    ON d.EmployerID=e.EmployerID AND d.ReportPeriod=@ReportPeriod

    GROUP BY e.EmployerID,e.EmployerName


    Thursday, August 7, 2008 8:42 PM