asked on

T SQL Coalesece instead of Case

This query calculates the AdjValue by subtracting TaxDepr from DpreciableBasis. To get the TaxDepr I call a function passing an EquipOid and a date. The date is converted to the 1st day of the next month. If the result of the function is 0.00 then I grab the value from another table. Can I use the Coalesce expression instead of a Case statement? How would this be done? Thanks.

                     ,AdjValue = isnull([ED1].TaxDepreciationDepreciableBasis,0)-

                              (
                              select

                              case
                              when      isnull(dbo.GetEquipmentTotalTaxDeprAmtFN
                                    (E1.EquipmentOid,CONVERT(varchar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'),0.00) = 0.00

                              then isnull([ED1].TaxDepreciationLifetime,0.00)

                              else
                                    (dbo.GetEquipmentTotalTaxDeprAmtFN
                                    (E1.EquipmentOid,CONVERT(varchar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'))


                              end
                              )

Shaun Kline

You shouldn't need the Coalesce function. The IsNull function should be sufficient:
isnull(dbo.GetEquipmentTotalTaxDeprAmtFN(E1.EquipmentOid,CONVERT(varchar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'), dbo.GetEquipmentTotalTaxDeprAmtFN(E1.EquipmentOid,CONVERT(varchar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'))

If you have a concern that the first part of the ISNULL function will return 0.00, you can wrap that part up in the NullIF function.

Kyle Abrahams, PMP

can GetEquipmentTotalTaxDeprAmtFN actually return 0? (not null)

Coalesce will only help you if you're returning null values.

ryann

ASKER

My Case statement works fine. A person told me that Coalesce would be another option I could use and would make the code easier to read. I am just curious how I would code this query using coalesce instead of a Case statement.

Shaun Kline

Missed the second IsNULL.

You can do this:
COALESCE(dbo.GetEquipmentTotalTaxDeprAmtFN
(E1.EquipmentOid,CONVERT(varchar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'), [ED1].TaxDepreciationLifetime,0.00)

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ryann

ASKER

Thanks!