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.

convert int to money without decimal and cents

I am doing the following to change an int into money (I want the commas) but it is adding a decimal and 2 zeroes at the end:
convert(varchar,convert(money,m.[FirstTier]),1) as 'First Tier',

m.firsttier = '1583456'
after conversion = '1,583,456.00'

Is there any easy way to not have the '.00'?

Thanks.this trick should do:
parsename(convert(varchar,convert(money,m.[FirstTier]),1),2) as 'First Tier'|||That worked... thanks!
|||Note that PARSENAME returns unicode string so you need to be careful when using that expression in WHERE clause for example. (for example if the column against which you are comparing is indexed and it is varchar , then with this expression the column will be converted to unicode) On the other hand, if this is for display purposes then it is better done in the client-side. Doing it on the client-side, you can handle other regional settings also.