IIF to SQL server

[wingnut]

I am in the process of upsizing an Access database to SQL server but I cannot get my had around changing a query that uses IIF to the equivalent SQL server syntax. I understand you should use Case?

The query is

SELECT tblPrices.PropID, Min(IIf([tblPrices.PricePerPerson]=False,[tblPrices.PriceStandard]/[tblProperties.PropOccupancy],[tblPrices.PriceStandard])) AS MinPrice, tblProperties.ResortID, tblResorts.ResortName
FROM tblResorts INNER JOIN (tblProperties INNER JOIN tblPrices ON tblProperties.PropID = tblPrices.PropID) ON tblResorts.ResortID = tblProperties.ResortID
GROUP BY tblPrices.PropID, tblProperties.ResortID, tblResorts.ResortName;

TIA

Andrew

[Lilliabeth]

SELECT
tblPrices.PropID,
MIN( CASE
WHEN ([tblPrices.PricePerPerson] = 0)
THEN [tblPrices.PriceStandard]/[tblProperties.PropOccupancy]
ELSE [tblPrices.PriceStandard]
END
) AS MinPrice,
tblProperties.ResortID,
tblResorts.ResortName
FROM
tblResorts
INNER JOIN (tblProperties
INNER JOIN tblPrices ON tblProperties.PropID = tblPrices.PropID)
ON tblResorts.ResortID = tblProperties.ResortID
GROUP BY
tblPrices.PropID,
tblProperties.ResortID,
tblResorts.ResortName;
------
You're correct - no IIF in SQL Server. You can't say FALSE, either.

[aspdaddy]

I would simpilfy the query & select both PricePerPerson and PriceStandard and do the logic in the application rather than the database.

The problem I find using CASE is 1) Performance is bad 2) Stops you editing the queries visually.

You could also add tblProperties.PriceModel and get rid of PricePerPerson. Multiply PriceStandard X 1 When PriceModel=Standard. Something like this

SELECT Price * CASE When PriceModel=1 THEN 1 ELSE PropOccupancy END AS TotalPrice

[Lilliabeth]

Yep, the original poster could choose to totally re-engineer.

[aspdaddy]

Hardly re-enginnering adding a field, & upsizing from access is a good time to fix your data model.

[wingnut]

Thanks for your answers, yes I think it would be far easier just to add a field than to hold the Price Per Person. Is it not often the best answer is the simplest :)

Thx

Andrew