SQL Case Statement in Where Clause

Question

I have a table with accounting_period in the form of 201101 for Jan 2011, 201102 for Feb 2011, etc.
I am trying to sum a column (eff_cc) for the quarter. That is, I want to get the sum the data for Jan, Feb & Mar 2011 for a date in the 1st quarter, etc.

So, I used a Case statement in the where clause. Basically I said (in the where clause) that:

• if the current month is 1,4,7 or 10, then get data from that month;
• if the current month is 2,5,8 or 11, then get the data from the curent month & the previous month; and
• if the current month is 3,6,9, or 12 then get data from the current and the previous two months

Doesn't want to work. The code is below.

select phase_code, accounting_period, sum(eff_cc) as BD_Eff_QTD, 
from prj_detail
where 
    case month(getdate()) % 3
        when  1 then    -- current month is 1,4,7,10
            accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2)
        when 2 then     -- current month is 2, 5, 8, 11
            (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
            accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2))
        when 3 then     -- current month is 3, 6, 9, 12
            (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
            accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2) or
            accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-2),2))
    end
group by phase_code, accounting_period

Answer 1

Thanks to all of you for the prompt and useful responses (without being condescending)

I kinda devised a solution based on your input. In essence, I created a subquery, with the relevant data, and a new column (Qtr). This column evaluates the accounting_period, and assigns 1,2,3 or 4 to each row.
Then, I wrapped another select around this subquery, with a where clause comparing the 'Qtr' to the current quarter (from getDate)

select phase_code, sum(BD_Eff_QTD) as BD_Eff_QTD
from 
(
select phase_code, accounting_period, sum(eff_pc) as BD_Eff_QTD,
'Qtr' = 
case
when cast (substring(convert(varchar, accounting_period),5,2) as int) <= 3 then 1
when cast (substring(convert(varchar, accounting_period),5,2) as int) <= 6 then 2
when cast (substring(convert(varchar, accounting_period),5,2) as int) <=9 then 3
else 4
end
from prj_detail
group by phase_code, accounting_period
) X
where CurQtr = datepart(qq,getDate()) 
group by phase_code

Maybe this is inefficient, but I run this only once a week, so performance is not a big issue. Thanks again to all.

Answer 2

Try this(I assume its SQL Server):

SELECT phase_code, 
       accounting_period, 
       SUM(eff_cc) OVER(PARTITION BY phase_code, yr, qt )AS bd_eff_qtd
  FROM   (SELECT a.*, 
                 CAST(Substring(accounting_period, 1, 4) AS INT)     yr, 
                 (CAST(Substring(accounting_period, 5, 2) AS INT) - 1)/ 3 qt 
          FROM   prj_detail a) a 

e.g:

CREATE TABLE #prj_detail
(
 phase_code VARCHAR(10),
 accounting_period  VARCHAR(10),
 eff_cc INT
)
INSERT INTO #prj_detail
SELECT '1', '201101', 1
UNION
SELECT '1', '201102', 2
UNION
SELECT '1', '201103', 2
UNION
SELECT '1', '201104', 1
UNION
SELECT '1', '201105', 1
UNION
SELECT '1', '201106', 1
UNION
SELECT '1', '201107', 3


SELECT phase_code, 
       accounting_period, 
       SUM(eff_cc) OVER(PARTITION BY phase_code, yr, qt )AS bd_eff_qtd
  FROM   (SELECT a.*, 
                 CAST(Substring(accounting_period, 1, 4) AS INT)     yr, 
                 (CAST(Substring(accounting_period, 5, 2) AS INT) - 1)/ 3 qt 
          FROM   #prj_detail a) a 

Answer 3

You could use CTE for this:

(I also made an assumption of using a transaction date instead of getdate() for all entries)

CREATE TABLE prj_detail
(phase_code VARCHAR(10)
, transaction_date DATETIME
, eff_cc INT)

INSERT INTO prj_detail
SELECT 'c',GETDATE(),11000
UNION ALL SELECT 'a',GETDATE(),1100
UNION ALL SELECT 'b','01/01/2010',2100
UNION ALL SELECT 'c','01/01/2009',500
UNION ALL SELECT 'a','05/01/2010',7800
UNION ALL SELECT 'b','07/01/2008',6000


WITH PhaseCode (phase_code, accounting_period, eff_cc)

AS 

(SELECT phase_code
,  case month(transaction_date) % 3
        when 1 then    -- current month is 1,4,7,10
            right(Year(transaction_date),4) + Right('0' + rtrim(month(transaction_date)),2)
        when 2 then     -- current month is 2, 5, 8, 11
            right(Year(transaction_date),4) + Right('0' + rtrim(month(transaction_date)-1),2)
        when 3 then     -- current month is 3, 6, 9, 12
            right(Year(transaction_date),4) + Right('0' + rtrim(month(transaction_date)-2),2)
    END accounting_period
, eff_cc
from prj_detail)

SELECT phase_code, accounting_period, SUM(eff_cc) AS BD_Eff_QTD
FROM PhaseCode
GROUP BY phase_code, accounting_period

Results, after inserting the rows a few times:

phase_code  accounting_period   BD_Eff_QTD
b   200807  12000
c   200901  1000
b   201001  4200
a   201004  15600
a   201101  13200
c   201101  11000

Answer 4

That is not the right way to write a CASE statement, as it is, it returns a BOOLEAN, which in SQL Server cannot stand alone. Just split them into 3 OR clauses

select phase_code, accounting_period, sum(eff_cc) as BD_Eff_QTD
from prj_detail
where 
(    month(getdate()) % 3 = 1 AND -- current month is 1,4,7,10
    accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2))
    OR
(    month(getdate()) % 3 = 2 AND -- current month is 2, 5, 8, 11
    (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
    accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2)))
    OR
(    month(getdate()) % 3 = 2 AND -- current month is 3, 6, 9, 12
    (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
    accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2) or
    accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-2),2)))
group by phase_code, accounting_period