Converting a MySQL multiple column subselect to SQL Server

I am adding SQL Server support to an application that currently uses
MySQL 4.1.
I have a table that looks like this in SQL Server 8.0:
CREATE TABLE dbo.sales_estimates
(
ID int NOT NULL,
YearMonth datetime NULL,
CountryCode char(3) NULL,
StoreCode int NULL,
SalesEstimate decimal(18, 0) NULL,
UserID int NULL,
DateTimeStamp datetime NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.sales_estimates ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
GO
It contains multiple sales estimates for stores, eg different users can
enter their own SalesEstimate for each store's monthly sales.
I want to select the most recent sales estimate for each store for a
given month.
In MySQL 4.1 I can do this by with the folling nested selects:
select YearMonth, CountryCode, StoreCode, SalesEstimate from
store_estimates
where (YearMonth,CountryCode, StoreCode, DateTimeStamp)
in (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode)
I'd like to write a similar statement for SQL Server (version 8.0) if
this is possible, but it appears that I can't have multiple rows in
subselects. Ideally I'd like to find a simpler query that works for
both DBs.SQL Server doesn't support the syntax, I believe it's referred
to as 'row constructors'
You can do this instead
select a.YearMonth, a.CountryCode, a.StoreCode, a.SalesEstimate
from store_estimates a
inner join (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode) b(YearMonth,CountryCode,
StoreCode, DateTimeStamp)
on a.YearMonth=b.YearMonth and a.CountryCode=b.CountryCode
and a.StoreCode=b.StoreCode and a.DateTimeStamp=b.DateTimeStamp
You can also achieve the same results with an EXISTS clause.