Need Help w/ Select w/ Multiple Joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help w/ Select w/ Multiple Joins

Author

Topic

bwol
Starting Member

19 Posts

Posted - 2008-08-07 : 09:52:33

Hi,

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'

When I execute the code provided below, I get the following results:

EmployerID------EmployerName-------QR-------------Dues
1000------------Employer A----------2008-07-01-----2008-07-02
1001------------Employer B----------2008-07-01-----2008-07-02

However, the desired results are:

EmployerID------EmployerName-------QR-------------Dues
1000------------Employer A----------2008-07-01----2008-07-02
1001------------Employer B----------2008-07-01
1002------------Employer C-------------------------2008-07-02

Here is the code. Any suggestions are much appreciated!!


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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-07 : 10:08:31

[code]
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
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bwol
Starting Member

19 Posts

Posted - 2008-08-07 : 16:11:50

Excellent, that did the trick!

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources