I got rid of the Select Top 1 statement and that worked and the consultant table is the first in the FROM clause so I am a little confused now.

The issue of getting rid to the SELECT TOP 1 clause is then it returns multiple records in the subquery which is a no no.

I guess I didn't get all the spec, they said that I needed to return the consultantID and ConsultantName even if the initial recordset came back empty:

SELECT o.orderNumber

,c.consultantid

,(Select top...

Cool. That works great. Thanks a lot too everybodies invaluable input.

The table that the address is kept is has multiple records per consultantID in which some of the addresses are blank:

select consultantid, max(effectivedate),c.BillToAddressLine1

,c.BillToAddressLine2

,c.BillToCity

,c.BillToState

,c.BillToZip

,c.BillToPhone

from consultant c where consultantID = '0000028'

GROUP...

Wow, thanks for the great information. The Date columns are declared as DATETIME. I format them in the code to only display the date (01/01/2008).

That worked fine. Thanks.

OK, make since but I am having an issue with the syntax:

DECLARE @ConsultantID VARCHAR(20)

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @Year CHAR(4)

SET @COnsultantID = '0000003'

SET @StartDate = NULL

SET @EndDate = NULL

SET @Year...

SELECT CAST('2007' AS DATETIME) gave me 2007-01-01 00:00:00.000

which is correct but

SELECT DATEADD(yy,1,CAST('2007' AS DATETIME)) gave me 2008-01-01 00:00:00.000 which is incorrect. I need 2007-12-31.

That was the issue. I added

OR OrdercreateDate IS NULL

And it works now. Thanks for all the help.

Still gives me a empty record set.

FROM Consultant H

LEFT OUTER JOIN [uvw_OrderListingSummary] A ON

h.consultantid=a.[ConsultantID]

LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON

A.OrderGroupNumber = B.OrderGroupNumber

ANDA.OrderNumber = B.OrderNumber

LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS...

I think I am doing that. This is the FROM clause:

FROM uvw_OrderListingSummary A

LEFT OUTER JOIN Consultant h ON

a.consultantid=h.[ConsultantID]

LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON

A.OrderGroupNumber = B.OrderGroupNumber

ANDA.OrderNumber = B.OrderNumber

LEFT OUTER...

I am posting what is happening in RS.

The proc is running a Reporting Service report and in the header information of the report we have the consultant name which is based on a consultantid parameter coming in,...

Perfect. Thanks a lot:D