SQL Case Statement in Where Clause?

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.

I thought of something like this too, but the OP replied that he wanted the Quarter-to-Date, not each quarter. – Lamak Feb 2 at 22:21 @Lamak - it clicked, after a while of re-reading the question – Richard aka cyberkiwi Feb 2 at 22:26 Thanks, That's eactly it..I am looking for Quarter-to-Date data for the current quarter. – R Dev Feb 2 at 22:36.

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 be 200807 12000 c 200901 1000 be 201001 4200 a 201004 15600 a 201101 13200 c 201101 11000.

Incorrect syntax near then – R Dev Feb 2 at 22:41 edited and tested. Should work now. – Vinnie Feb 2 at 22:45.

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.

– Cybernate Feb 2 at 22:36 Don't know what DDI is. Sample DAta: Phase_Code, accounting_period, eff_cc ---------- ----------------- ----- AAMC 201101 25.32 AAMC 201102 13.35 AAMC 201104 15.65 ATT 201101 25.00 So for the above data set, teh result should be: AAMC 38.67 (= 25.32 + 13.35), because we are in teh 1st quarter ATT 25.00 – R Dev Feb 2 at 22:40 Updated the post with the DDL and sample data I used. Try it... – Cybernate Feb 2 at 22:42.

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).

It is common knowledge that you can apply a Case statement in the SELECT or ORDER BY portion of a SQL statement. What isn’t well known is that you can use it effectively in a WHERE clause. I want to show all orders to every employee unless the order is assigned to employeeid 263, EXCEPT if the person trying to view all orders IS employeeid 263 or if it is that persons boss.

In that case, show every order. This appears really simple and could be handled in a where clause, but I couldn’t figure it out. The first when says “if you are #263 or #181, set employeeid = employeeid, which will always be true and will the order.

The second when says, “if you aren’t #263 or #181, and the current order is assigned to #263, then set t.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions

» About WebKnox
» Contact
» WebKnox Blog
» RSS
» Newsletter
» Twitter
» API
» API Showcase