Access statement using IIF, need help converting to SQL

[Ted Young]

I have an ACCESS SQL statement:

SELECT Sum(IIF(GLTYPE = "R",-1 * Amount,Amount)) as SUMOFAMOUNT

Is there a way to write this in SQL SERVER? I think I need to use the CASE
statment but I am not sure of what the code should look like.

Any ideas or suggestions would be greatly appreciated.

Thanks!

Ted

[DaveSatz]

see http://www.mssqlserver.com/faq/development-iif.asp
"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b6aa3f7$1@news.devx.com...
>
> I have an ACCESS SQL statement:
>
> SELECT Sum(IIF(GLTYPE = "R",-1 * Amount,Amount)) as SUMOFAMOUNT
>
> Is there a way to write this in SQL SERVER? I think I need to use the
CASE
> statment but I am not sure of what the code should look like.
>
> Any ideas or suggestions would be greatly appreciated.
>
> Thanks!
>
> Ted

[Ted Young]

Thanks for the reply. The thing I am stuck on is the SUM and the use of GLTYPE
in my query.

Here is the ACCESS QUERY I need to convert:

INSERT INTO tblWorkReceiptsSum ( AcctID, Month, SumOfAmount )
SELECT DISTINCTROW tblAmisysReceipts.AcctID, tblAmisysReceipts.Month, Sum(IIf(Left([GLType],1)="R",-1*[Amount],[Amount]))
AS SumOfAmount
FROM tblAmisysReceipts
GROUP BY tblAmisysReceipts.AcctID, tblAmisysReceipts.Month

What I have to far for my SQL stored procedure is:

CASE
WHEN SUBSTRING(GLType,1,1) = 'R' THEN Sum(-1 * Amount)
ELSE Sum(Amount)
END
FROM tblAmisysReceipts
GROUP BY AcctID, Month

I am getting an error that says GLTYPE is invalid becuase it is not contained
in an aggregate function or GROUP BY clause.

Any ideas on how to fix that error AND if my use of SUM is correct in my
SQL stored procedure.

Thanks for you help!!!

Ted



"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>see http://www.mssqlserver.com/faq/development-iif.asp
>"Ted Young" <tedyoung@connecticare.com> wrote in message
>news:3b6aa3f7$1@news.devx.com...
>>
>> I have an ACCESS SQL statement:
>>
>> SELECT Sum(IIF(GLTYPE = "R",-1 * Amount,Amount)) as SUMOFAMOUNT
>>
>> Is there a way to write this in SQL SERVER? I think I need to use the
>CASE
>> statment but I am not sure of what the code should look like.
>>
>> Any ideas or suggestions would be greatly appreciated.
>>
>> Thanks!
>>
>> Ted
>
>

[Ted Young]

I forgot the beginning of my SQL Stored Procedure...I modified the post and
it appears correct below................

Thanks for the reply. The thing I am stuck on is the SUM and the use of
GLTYPE
in my query.

Here is the ACCESS QUERY I need to convert:

INSERT INTO tblWorkReceiptsSum ( AcctID, Month, SumOfAmount )
SELECT DISTINCTROW tblAmisysReceipts.AcctID, tblAmisysReceipts.Month, Sum(IIf(Left([GLType],1)="R",-1*[Amount],[Amount]))
AS SumOfAmount
FROM tblAmisysReceipts
GROUP BY tblAmisysReceipts.AcctID, tblAmisysReceipts.Month

What I have to far for my SQL stored procedure is:
SELECT AcctID,Month,SumOfAmount =
CASE
WHEN SUBSTRING(GLType,1,1) = 'R' THEN Sum(-1 * Amount)
ELSE Sum(Amount)
END
FROM tblAmisysReceipts
GROUP BY AcctID, Month

I am getting an error that says GLTYPE is invalid becuase it is not contained
in an aggregate function or GROUP BY clause.

Any ideas on how to fix that error AND if my use of SUM is correct in my
SQL stored procedure.

Thanks for you help!!!

Ted



"Ted Young" <tedyoung@connecticare.com> wrote:
>
>Thanks for the reply. The thing I am stuck on is the SUM and the use of
GLTYPE
>in my query.
>
>Here is the ACCESS QUERY I need to convert:
>
>INSERT INTO tblWorkReceiptsSum ( AcctID, Month, SumOfAmount )
>SELECT DISTINCTROW tblAmisysReceipts.AcctID, tblAmisysReceipts.Month, Sum(IIf(Left([GLType],1)="R",-1*[Amount],[Amount]))
>AS SumOfAmount
>FROM tblAmisysReceipts
>GROUP BY tblAmisysReceipts.AcctID, tblAmisysReceipts.Month
>
>What I have to far for my SQL stored procedure is:
>
> CASE
> WHEN SUBSTRING(GLType,1,1) = 'R' THEN Sum(-1 * Amount)
> ELSE Sum(Amount)
> END
>FROM tblAmisysReceipts
>GROUP BY AcctID, Month
>
>I am getting an error that says GLTYPE is invalid becuase it is not contained
>in an aggregate function or GROUP BY clause.
>
>Any ideas on how to fix that error AND if my use of SUM is correct in my
>SQL stored procedure.
>
>Thanks for you help!!!
>
>Ted
>
>
>
>"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>>see http://www.mssqlserver.com/faq/development-iif.asp
>>"Ted Young" <tedyoung@connecticare.com> wrote in message
>>news:3b6aa3f7$1@news.devx.com...
>>>
>>> I have an ACCESS SQL statement:
>>>
>>> SELECT Sum(IIF(GLTYPE = "R",-1 * Amount,Amount)) as SUMOFAMOUNT
>>>
>>> Is there a way to write this in SQL SERVER? I think I need to use the
>>CASE
>>> statment but I am not sure of what the code should look like.
>>>
>>> Any ideas or suggestions would be greatly appreciated.
>>>
>>> Thanks!
>>>
>>> Ted
>>
>>
>

[DaveSatz]

Does this ex. help ?

USE Northwind
go

SELECT ShipperName
, DatePart( yyyy, OrderDate )
, DateName( mm, OrderDate ) AS Month
, SUM( CASE
WHEN Discount = 0 THEN ExtendedPrice
ELSE ( UnitPrice * Quantity )
END ) AS SumOfAmount
FROM Invoices
GROUP BY ShipperName
, DatePart( yyyy, OrderDate )
, DateName( mm, OrderDate )
go


--
HTH,
David Satz
SQL Server MVP
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b6aabca$1@news.devx.com...
>
> I forgot the beginning of my SQL Stored Procedure...I modified the post
and
> it appears correct below................
>
> Thanks for the reply. The thing I am stuck on is the SUM and the use of
> GLTYPE
> in my query.
>
> Here is the ACCESS QUERY I need to convert:
>
> INSERT INTO tblWorkReceiptsSum ( AcctID, Month, SumOfAmount )
> SELECT DISTINCTROW tblAmisysReceipts.AcctID, tblAmisysReceipts.Month,
Sum(IIf(Left([GLType],1)="R",-1*[Amount],[Amount]))
> AS SumOfAmount
> FROM tblAmisysReceipts
> GROUP BY tblAmisysReceipts.AcctID, tblAmisysReceipts.Month
>
> What I have to far for my SQL stored procedure is:
> SELECT AcctID,Month,SumOfAmount =
> CASE
> WHEN SUBSTRING(GLType,1,1) = 'R' THEN Sum(-1 * Amount)
> ELSE Sum(Amount)
> END
> FROM tblAmisysReceipts
> GROUP BY AcctID, Month
>
> I am getting an error that says GLTYPE is invalid becuase it is not
contained
> in an aggregate function or GROUP BY clause.
>
> Any ideas on how to fix that error AND if my use of SUM is correct in my
> SQL stored procedure.
>
> Thanks for you help!!!
>
> Ted
>
>
>
> "Ted Young" <tedyoung@connecticare.com> wrote:
> >
> >Thanks for the reply. The thing I am stuck on is the SUM and the use of
> GLTYPE
> >in my query.
> >
> >Here is the ACCESS QUERY I need to convert:
> >
> >INSERT INTO tblWorkReceiptsSum ( AcctID, Month, SumOfAmount )
> >SELECT DISTINCTROW tblAmisysReceipts.AcctID, tblAmisysReceipts.Month,
Sum(IIf(Left([GLType],1)="R",-1*[Amount],[Amount]))
> >AS SumOfAmount
> >FROM tblAmisysReceipts
> >GROUP BY tblAmisysReceipts.AcctID, tblAmisysReceipts.Month
> >
> >What I have to far for my SQL stored procedure is:
> >
> > CASE
> > WHEN SUBSTRING(GLType,1,1) = 'R' THEN Sum(-1 * Amount)
> > ELSE Sum(Amount)
> > END
> >FROM tblAmisysReceipts
> >GROUP BY AcctID, Month
> >
> >I am getting an error that says GLTYPE is invalid becuase it is not
contained
> >in an aggregate function or GROUP BY clause.
> >
> >Any ideas on how to fix that error AND if my use of SUM is correct in my
> >SQL stored procedure.
> >
> >Thanks for you help!!!
> >
> >Ted
> >
> >
> >
> >"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >>see http://www.mssqlserver.com/faq/development-iif.asp
> >>"Ted Young" <tedyoung@connecticare.com> wrote in message
> >>news:3b6aa3f7$1@news.devx.com...
> >>>
> >>> I have an ACCESS SQL statement:
> >>>
> >>> SELECT Sum(IIF(GLTYPE = "R",-1 * Amount,Amount)) as SUMOFAMOUNT
> >>>
> >>> Is there a way to write this in SQL SERVER? I think I need to use the
> >>CASE
> >>> statment but I am not sure of what the code should look like.
> >>>
> >>> Any ideas or suggestions would be greatly appreciated.
> >>>
> >>> Thanks!
> >>>
> >>> Ted
> >>
> >>
> >
>