Help with pretty basic select statement

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

General SQL Server Forums

New to SQL Server Programming

Help with pretty basic select statement

Author

Topic

bwol
Starting Member

19 Posts

Posted - 2008-07-17 : 13:44:52

I am trying to create a select statement that pulls data from two tables in the MSSQL 2000 DB that I am working with.

The first table is called employers and has two fields I am interested in: 'EmployerName' and 'EmployerID.'

The second table is called reports. There is one row for each report received and the fields I am interested in are 'EmployerID', 'DateReceived' and 'ReportPeriod'

I want to create a stored procedure with ReportPeriod as an input parameter. I want the procedure to return a table that has each EmployerName and EmployerID in the employers table as its first two columns.

For the third column I would like the earliest DateReceived value from the reports table where the EmployerID is equal to the EmployerID in the table being returned and where the ReportPeriod is equal to the ReportPeriod input variable.

Any suggestions are much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 13:52:55

[code]CREATE PROC YourProcedure
@ReportPeriod <datatypehere>
AS

SELECT e.EmployerID,e.EmployerName,MIN('DateReceived) AS MinDate
FROM employers e
INNER JOIN reports r
ON r.EmployerID=e.EmployerID
WHERE r.ReportPeriod=@ReportPeriod
GROUP BY e.EmployerID,e.EmployerName
GO[/code]

bwol
Starting Member

19 Posts

Posted - 2008-07-17 : 14:06:21

Thanks so much for the quick reply.

I have two follow-up questions.

1. Is "MIN('DateReceived)" (with only one single quote) the correct syntax or should it be "MIN('DateReceived')" (or "MIN(r.DateReceived)"))

2. What is the purpose of including "AS MinDate" at the end of the select line?

Thanks again for your help!

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-17 : 15:07:03

That is just an Alias Name for the Column
You can give any name you want.

bwol
Starting Member

19 Posts

Posted - 2008-07-17 : 16:28:13

Thanks to everyone who made suggestions. The stored procedure (as shown below) work great.

I have one final question.

Right now, the table returned by this stored procedure only includes employers where a match is found in the report table.

How can I modify this stored procedure so that all employers in the employers table are included 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?

Thanks again for the help.

CREATE PROCEDURE GetMonthlyReport
@ReportPeriod varchar(20)
AS

SELECT e.EmployerID,e.EmployerName,MIN(r.DateReceived) AS MinDate
FROM employers e
INNER JOIN reports r
ON r.EmployerID=e.EmployerID
WHERE r.ReportPeriod=@ReportPeriod
GROUP BY e.EmployerID,e.EmployerName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources