Convert Access query to sql

how to convert this access query to sql query

IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name

please......

case

when [Total Bunches] > 0 then Production * 1000 / [Total Bunches]

else 0

end

as Name2,

SUM(

case

when BlockInYield = -1 then [SIZE]

else 0

end

)

as Name1,

case when BlockInYield = 1 then (case when TC_M > 0 then TC_M else TC_DENS *[SIZE] end)

else 0

end

as Name

Thanks

Naras

|||There is no IFF operator in TSQL; as Naras is indicating the way to approach converting an IFF statement is to use an abstraction using CASE statements.sqlsql

Conversion Access Query to SQL Server 2000

SELECT DISTINCTROW OILPALM.NAME, formatyear([year]) AS yearDisplay, OILPALM.YEAR, OILPALM.UNIT, OILPALM.BLOCK, OILPALM.BlockInYield, [Y_01_total_ton]+[Y_02_total_ton]+[Y_03_total_ton]+[Y_04_total_ton]+[Y_05_total_ton]+[Y_06_total_ton]+[Y_07_total_ton]+[Y_08_total_ton]+[Y_09_total_ton]+[Y_10_total_ton]+[Y_11_total_ton]+[Y_12_total_ton] AS Production, IIf([m_a]>0,[Production]/[m_a],0) AS [Avg Yield], ([Y_TH_POT]*IIf([blockinyield]=True,[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100) AS TotalTon_Pot, IIf(IIf([blockinyield]=True,[SIZE],0)>0,([Y_TH_POT]*IIf([oilpalm].[blockinyield]=True,[oilpalm].[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100)/IIf([blockinyield]=True,[SIZE],0),0) AS [Yield pot], [Avg Yield]-[Yield pot] AS [Yield Gap], OILPALM.Y_KGB AS [Avg bunchweight], IIf([TotalTree]>0,[Total bunches]/[TotalTree],0) AS [Avg Bunch No], OILPALM.SIZE AS [Total size], OILPALM.Y_TOTAL_mandays AS [Total workforce], OILPALM.Y_AVG_harvest_int AS Avg_Harvest_int, OILPALM.Y_AVG_harvest_int AS sum_Harvest_int_sum, IIf([Y_AVG_harvest_int]>0,1,0) AS rec_Harvest_int, IIf([rec_Harvest_int]>0,[sum_Harvest_int_sum]/[rec_Harvest_int],0) AS Calc_Harvest_int, OILPALM.Y_per_manday AS Avg_YMD, [Y_01_TOTAL_bunches]+[Y_02_TOTAL_bunches]+[Y_03_TOTAL_bunches]+[Y_04_TOTAL_bunches]+[Y_05_TOTAL_bunches]+[Y_06_TOTAL_bunches]+[Y_07_TOTAL_bunches]+[Y_08_TOTAL_bunches]+[Y_09_TOTAL_bunches]+[Y_10_TOTAL_bunches]+[Y_11_TOTAL_bunches]+[Y_12_TOTAL_bunches] AS [Total bunches], OILPALM.Y_TOTAL_mandays AS [Total manday], IIf([Blockinyield]=True,IIf([TC_M]>0,[TC_M],[TC_DENS]*[size]),0) AS TotalTree, OILPALM.TC_M, IIf([TotalTree]>0,([Production]*1000)/[TotalTree],0) AS [Avg bunchweighMTree], IIf([Total workforce]>0,[Production]/[Total workforce],0) AS TMd, (IIf(([oilpalm]![blockinyield]=-1),[OILPALM]![SIZE],0)) AS M_A
FROM OILPALM INNER JOIN SYS_soil_type ON OILPALM.SOILTYPE = SYS_soil_type.SoilType
ORDER BY OILPALM.YEAR DESC , OILPALM.UNIT, OILPALM.BLOCK;

Please Help Me....

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||

1. Change all your IIF(CONDITION,TRUE STMT, FALSE STMT)

CASE WHEN CONDITION THEN TRUE STMT ELSE FALSE STMT END

2. You need to convert your formatyear macro logic to SQL Server