Using MAX DATE with Empty Space 2

[Iamthestig]

Can someone please help me out with this query. I get a error because one of the rows has an empty space in the YearEnd column.

SELECT 
    a.FirstName + ' ' + a.LastName AS FullName,
	a.Initial,
	a.Age,
	b.Level2Company,
	b.YearEnd,
	
FROM tblContacts a

INNER JOIN
	tblSalary AS b ON a.CREF = b.CREF

WHERE

	a.CREF = @CREF AND CAST('01/' + b.YearEnd AS DATE) =

	(SELECT Max(CAST('01/' + c.YearEnd AS DATE))  FROM tblSalary c WHERE c.CREF = @CREF)

YearEnd is a varchar(7)

Thanks for any help.

 

[gmmastros]

You could wrap your YearEnd columns with NullIf.

Ex: NullIf(b.YearEnd, '')

This will cause the value NULL to be returned when YearEnd is an empty string. When you concatenate a NULL with a string (like you are doing in your code), the result is null. In fact, NULL will propagate all the way through your code and allow it to work the way you want it to.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[imex]

Try:

with CTE_Max as
(
    select top 1 
        Level2Company,
	YearEnd
    from tblSalary 
    where
        CREF = @CREF
    order by
        RIGHT(YearEnd, 4) + LEFT(YearEnd, 2) desc
)

select 
    a.FirstName + ' ' + a.LastName AS FullName,
    a.Initial,
    a.Age,
    m.Level2Company,
    m.YearEnd
FROM tblContacts a
CROSS JOIN CTE_Max as m
WHERE
    a.CREF = @CREF

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Iamthestig]

Many thanks to both of you for resolving this. I used George's answer as it is easier to insert into the code.

 

[djj55]

You may not have the problem but we use:

ISNULL(a.FirstName + ' ', '') + ISNULL(a.LastName,'') AS FullName,

which prevents a null in FullName

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

djj,

That would make a great computed column, right?

Iamthestig,

You could also create a computed column on your table to make this sort of process easier.

Computed columns are cool because they can hide ugliness for you and don't take any storage space unless you use the computed column in an index.

For example, you could do this:

Alter table tblContacts 
add FullName As (ISNULL(FirstName + ' ', '') + ISNULL(LastName,''))

Now, when you query the table, you will see a FullName column. The FullName value is based on the expression used to create the full name column. There's nothing you will ever need to do to keep the FullName accurate because every time you use the full name column, SQL Server will actually use the express to get the data.

You could do something similar for your dates:

Alter table tblSalary 
Add TheYear As CAST('01/' + NullIf(b.YearEnd, '') AS DATE)

Now you can use the column named "TheYear" in place of the expression, like this:

SELECT  a.FullName,
	a.Initial,
	a.Age,
	b.Level2Company,
	b.YearEnd
FROM    tblContacts a
        INNER JOIN tblSalary AS b 
          ON a.CREF = b.CREF
WHERE	a.CREF = @CREF 
        AND b.TheYear = (SELECT Max(c.TheYear) FROM tblSalary c WHERE c.CREF = @CREF)

You can even add an index on the computed column to help speed up the query. Somthing like this:

Create NONCLUSTERED Index idx_tblSalary_CREF_TheYear
On tblSalary(CREF, TheYear)

This index will likely speed up your query, although it may not be noticeable if the query is already fast.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom