2 possible things

The query is returning multiple rows, and the 'N' is being returned because of the last row.

OR/AND

Your performing a Left Join,

But you referencing T2.[Date Period] in your Where clause effectively makings it an inner join.

Unless the the value for Application ID and Server Name are NULL in the T2 table It will never evaluate to 'Y'

This is why we need sample data and DDL which would allow us to see these things and provide a better answer.

SELECT

T2.[Application ID]

, T2.[ServerName]

,[TB Billed This Month]

,CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END

FROM TableName1 T1

LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]

AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'