Date: 04/17/06 (SQL Server)    Keywords: no keywords

SELECT a.ProvID, a.Category, a.DRGs, a.RevCode, a.TypeID, a.Rate AS MarketRate, b.Rate AS ContractRate, ' ' as MarketLevel2Rate, ISNULL(a.Comment, 'None') AS Comment, a.LineEffective, a.LineExpiration
INTO CombinedRateData
FROM RateData a
LEFT OUTER JOIN RateData b ON a.ProvID = b.ProvID AND a.Category = b.Category AND a.DRGs = b.DRGs AND a.RevCode = b.RevCode AND a.TypeID = b.TypeID AND ((a.Comment = b.Comment) OR (a.Comment IS NULL AND b.Comment IS NULL))
AND a.LineEffective = b.LineEffective AND a.LineExpiration = b.LineExpiration AND b.RateTypeID = 2
WHERE a.RateTypeID = 1
GROUP BY a.provid, a.category, a.DRGs, a.RevCode, a.TypeID, a.Comment, a.LineEffective, a.LineExpiration, a.Rate, b.Rate


okay... I have a table. It presently has ProvID, Category, DRGs, RevCode, TypeID, Rate, Comment, LineEffective, LineExpiration, RateTypeID.

I need it to have ProvID, Category, DRGs, RevCode, TypeID, MarketRate, ContractRate, Comment, LineEffective, LineExpiration.

The difference is that Market Rates have a RateTypeID of 1, and Contract Rates have a RateTypeID of 2. Thus, the query above.

The problem is, no matter how I play with the RateTypeID, where I put it, etc., I can never get ContractRate to populate everywhere it should. It doesn't matter where I put "RateTypeID = ", it still doesn't manage to find the two columns-- MarketRate will be filled, and ContractRate will be NULL.

EDIT: This is now only happening when the two Rates are identical. One hospital with a Market rate of 7% and a Contract rate of 8% is now filling fine; a hospital whose market and contract are both $1050 per diem is giving me market 1050 and contract NULL. Grrrrr.

Source: http://community.livejournal.com/sqlserver/47100.html