Nested Case Statement in Where clause

building semantic layer
1

Hello All

We are using BO 4.1 SP5, and DB is MS SQL Server 2012

I need a help in the case statement in where clause, wherein I have to calculate a measure based on a fiscal year from Nov of a year to Oct of next year.

while prompting the users, ‘Today’ should be the default value followed by other dates in a list, that i am taking care in LOV’s and parameter section,

problem is -

  1. case Statement is failing at the 3rd WHEN condition , (‘today’ <> ‘today’ and datepart(mm,‘today’)=‘12’)
    from today how to get the mm out

  2. If we are selecting a date, it is working fine, but as soon as we select today, it fails

  3. what I am expecting is, if I am selecting today, this condition should be ignored.

  4. Is there any other easy way of doing it.

could you help me in finding where I am lacking. :expressionless:
Thanks for all the help in advance. :+1:

SELECT
D.DATEFORMAT
FROM
fct F INNER JOIN Date D ON (D.DATEID=F.DATEID)
WHERE
( DATE BETWEEN
CASE
WHEN ‘Today’=‘Today’ AND CAST(DATEPART(mm,getdate()) AS VARCHAR(10))=‘12’
THEN cast(cast(datepart(YYYY,DATEADD(YYYY,0,getdate())) as VARCHAR(10)) +’-11-01’ AS VARCHAR(10))
WHEN ‘Today’=‘Today’ AND CAST(DATEPART(mm,getdate()) AS VARCHAR(10))<> ‘12’
THEN CAST(CAST(DATEPART(YYYY,DATEADD(YYYY,-1,getdate())) as VARCHAR(10)) +’-11-01’ AS VARCHAR(10))
WHEN ‘Today’<>‘Today’ AND CAST(DATEPART(mm,‘Today’) AS VARCHAR(10))= ‘12’
THEN cast(cast(datepart(YYYY,DATEADD(YYYY,0,getdate())) as VARCHAR(10)) +’-11-01’ AS VARCHAR(10))
ELSE CAST(CAST(DATEPART(YYYY,DATEADD(YYYY,-1,‘Today’)) as VARCHAR(10)) +’-11-01’ AS VARCHAR(10))
END
AND
CASE
WHEN ‘Today’=‘Today’ AND CAST(DATEPART(mm,getdate()) AS VARCHAR(10)) =‘12’
THEN CAST(CAST(DATEPART(YYYY,DATEADD(YYYY,+1,GETDATE())) AS VARCHAR(10)) +’-10-31’ AS VARCHAR(10))
WHEN ‘Today’=‘Today’ AND CAST(DATEPART(mm,getdate()) AS VARCHAR(10)) <> ‘12’
THEN CAST(CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR(10)) +’-10-31’ AS VARCHAR(10))
WHEN ‘Today’<>‘Today’ AND CAST(DATEPART(mm,‘Today’) AS VARCHAR(10))=‘12’
THEN CAST(CAST(DATEPART(YYYY,DATEADD(YYYY,+1,GETDATE())) AS VARCHAR(10)) +’-10-31’ AS VARCHAR(10))
ELSE cast(cast(datepart(YYYY,‘Today’) AS VARCHAR(10)) +’-10-31’ AS VARCHAR(10))
END )

zgoyal14 :india: (BOB member since 2011-08-03)

2 
DATEPART(mm,'Today')

Seems wrong, you cant extract a month from the word ‘Today’

Derf :canada: (BOB member since 2011-05-16)

3

I have solved this,

As code was failing at ‘else’ part, I have removed it and instead I used ‘WHEN’, ‘THEN’ and there is no else.
All conditions in WHEN and solutions in THEN
thats it.
Thanks anyways. :mrgreen:

zgoyal14 :india: (BOB member since 2011-08-03)