how to sum iif?

[ciqeane]

Hi. i want to sum the iif value and i dont know the right way to do that.
i attached the sample data in sum iif(zip file) please help me solve this problem.

[NeoPa]

There's really not much point in attaching the details of your question in this forum (I would guess most forums really). If you have a question, then please write it out and post it in the forum.

IIf() is not a standard SQL function. I believe there is a version of it (IF()) in Report Builder, but nothing in T-SQL itself.

[ck9663]

This is the closest you can have to an IIF() function...


Happy Coding!!!

~~ CK

[ciqeane]

seriously i dont know how to sum iif. so i made a dataset for each GAS, LIQUID, OIL, OTH.
a simple dataset, something like below:

select sum(t_oamt) as Gtotal from
temp_table
where month = @month and year= @year and DanielProd = 'GAS'

so i just sum the dataset value,
Sum(Fields!Gtot al.Value, "Gtotal")

Thanks all.

[NeoPa]

We did try to point you in the right direction.

It seems a bit more direction is required so have a play with this and see if it helps you understand the issue a little more clearly :

SELECT SUM(CASE
               WHEN [DanielProd] = 'GAS' THEN [T_OAmt]
               ELSE 0
           END) AS [GasTotal]
     , SUM(CASE
               WHEN [DanielProd] = 'LIQUID' THEN [T_OAmt]
               ELSE 0
           END) AS [LiqTotal]
     , SUM(CASE
               WHEN [DanielProd] = 'OIL' THEN [T_OAmt]
               ELSE 0
           END) AS [OilTotal]
     , SUM(CASE
               WHEN [DanielProd] = 'OTH' THEN [T_OAmt]
               ELSE 0
           END) AS [OthTotal]
FROM   [YourTable]
WHERE  [Month] = @Month
  AND  [Year] = @Year

PS. Thank you for selecting my post as Best Answer, but you may want to reconsider as it's not much of a solution really. Please consider one of the other posts instead.

[ciqeane]

many thanks to all. appreciate:)

[NeoPa]

You're welcome :)

I assume this latest SQL example was of help to you and you were able to get something working as originally intended?