Complex Case Statement in View in SQL Server 2000

I am working on SQL Server 2000.
I am trying to create a Case Statement where I do something like the
following:
I want to set up the case statement to check if a value falls within a
specific range then sum the unit amount as the type of data it
represents.
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
CASE ACCOUNT WHEN >= 51110 AND <= 51140 THEN
SUM(UNIT) AS BudFTEProd
WHEN >= 51210 and <= 51240 THEN
SUM(UNIT) AS BudFTEProdOth
WHEN >= 51310 and <= 51340 THEN
SUM(UNIT) AS BudFTENonProd
END
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
HAVING (FISCAL_YEAR = 2006)
Anyone know how I would do this?
Thanks,
DebbieTry:
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
SUM (CASE WHEN ACCOUNT >= 51110 AND ACCOUNT <= 51140 THEN
UNIT ELSE 0 END) AS BudFTEProd,
SUM (CASE WHEN ACCOUNT >= 51210 and ACCOUNT <= 51240 THEN
UNIT ELSE 0 END) AS BudFTEProdOth,
SUM(CASE WHEN ACCOUNT >= 51310 and ACCOUNT <= 51340 THEN
UNIT ELSE 0 END) AS BudFTENonProd
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
WHERE (FISCAL_YEAR = 2006)
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Debbie" <debsmithmich@.hotmail.com> wrote in message
news:1156263058.634273.58140@.m79g2000cwm.googlegroups.com...
I am working on SQL Server 2000.
I am trying to create a Case Statement where I do something like the
following:
I want to set up the case statement to check if a value falls within a
specific range then sum the unit amount as the type of data it
represents.
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
CASE ACCOUNT WHEN >= 51110 AND <= 51140 THEN
SUM(UNIT) AS BudFTEProd
WHEN >= 51210 and <= 51240 THEN
SUM(UNIT) AS BudFTEProdOth
WHEN >= 51310 and <= 51340 THEN
SUM(UNIT) AS BudFTENonProd
END
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
HAVING (FISCAL_YEAR = 2006)
Anyone know how I would do this?
Thanks,
Debbie