Use Most Recent Date Modified

[Stuugie]

Hi All,

I am creating a simple query with a not so simple twist, at least it's not simple to me. First, the query:

select distinct b.Bank_Mnemonic as [Bank],bd.Demo_Name as [Demography],ei.BEFI_Description[Economic Indicator],bsd.BSD_Year as [Year],bsd.BSD_Data_Value as [Value]
from dbo.tblBank_Source_Data as bsd 
inner join
dbo.tblBankDemographics as bd
on
bsd.Demo_ID=bd.Demo_ID
inner join
dbo.tblBanks as b
on
bsd.Bank_ID=b.Bank_ID
inner join
dbo.tblEconomicFinancialIndicators as ei
on
bsd.BEFI_ID=ei.BEFI_ID
where (bd.Demo_Name='Canada' and bsd.BSD_Quarter=0
and b.Bank_Mnemonic='BMO'
and bsd.BSD_Date_Modified ='2013-07-23')
order by ei.BEFI_Description

The spot where I need help with is the bsd.BSD_Date_Modified = '2013-07-23' in that what I really need is the most recent date modified value without having to hard code it as a date like I did. Is this even possible? I'm going to play around with assigning variables but even at that I am not very sure how to do that. It is important to note that the most recent date modified is most important as there can be a 2013-07-11 that would be useless if the 07-23 exists. Am I being clear enough? I really hope so.

[Stuugie]

Alright so I've been trying something like

declare @MostRecentDate date;
--set @MostRecentDate=MAX(dbo.tblBank_Source_Data.BSD_Date_Modified)
select distinct b.Bank_Mnemonic as [Bank],bd.Demo_Name as [Demography],ei.BEFI_Description[Economic Indicator],bsd.BSD_Year as [Year],bsd.BSD_Data_Value as [Value]

but I had to comment out the Set becauseit cannot be bound. I tried moving the variable around to set it in other places within the quiery but have had no luck.

[pritaeas]

Can't you just replace that value with a sub-select?

and bsd.BSD_Date_Modified = (SELECT MAX(BSD_Date_Modified) FROM dbo.tblBank_Source_Data)

[Stuugie]

I could kiss your avi pritaeas, that works like a charm. I think my biggest problem with SQL is that I way over think things, I had similar problems with queries while taking database courses in university. I did well in those courses, especially with db design but with querying I always seemed to think the statements were harder than what was always required.

Anyways, I really REALLY appreciate your help again.