Hi,
I am visualizing one long case statement. Is there a way to simplify this? I greatly appreciate your help. Thanks!
First Half: < 6/30
Second Half: >6/30
Q1: < 3/31
Q2: between 4/1 and 6/30
Q3: between 7/1 and 9/30
Q4: >9/30
Early Jan: Jan 1-9
Mid Jan: Jan 10-18
Late Jan: Jan 19-30
Early Feb…Dec
Mid Feb…Dec
Late Feb…Dec
w2s2b1 (BOB member since 2008-05-14)
Hi,
I am not sure I understand what kind of help you are expecting to get. 
Can you please describe what you are trying to achieve, what you did/used and why it failed or was not sufficient?
Marek Chladny 
(BOB member since 2003-11-27)
There is not a simple way to handle this on the report or the universe level.
This is all data that should be held within a Date dimension in a data warehouse. If you do not have a date dimesion, I highly recommend building one. It is very simple to create and requires almost no maintenance.
A date dimension, in common practice, will take all calendar dates and have mutliple descriptive columns that describe the date in more detail. These details could include: [Day of Month],[Day of Year],[Quarter of Year],[First Day of Month Indicator],[Last Day of Month Indicator],[Beginning Portion of Month],[Middle Portion of Month], etc…
Here is a really simple way to create a basic date dimension:
Ryan Bierschenk 
(BOB member since 2009-01-26)
Create the attached table in your data-source. The other dates in your db will fall between the begin and end M/D.
Dates_Table.xls (30.0 KB)
johnmcand 
(BOB member since 2009-05-29)
Opss…
Here’s what I’m trying to do.
CASE
WHEN startdate < 6/30 THEN “FIRST HALF”
WHEN startdate > 6/30 THEN “SECOND HALF”
WHEN startdate < 3/31 THEN “QUARTER 1”
WHEN startdate between Jan 1 and Jan 9 then “EARLY JANUARY”
WHEN startdate between Jan 10 and Jan 18 then “MID JANUARY”
WHEN startdate…
I’m trying to see if there’s a better way of doing this than having a huge case statement for the two halves of the year, four quarters of the year and 12 months times 3 date ranges (Early, Mid, Late). 
w2s2b1 (BOB member since 2008-05-14)
The problem with a case statement is that the date can be more than one of your conditions at once, but using a case will only return you the first ‘True’ value. Take, January 1st for example, it is both ‘FIRST HALF’ and ‘QUARTER 1’, but this case statement will only return ‘FIRST HALF’.
I still think the best way to handle this is my post above, which is creating a date dimension with the descriptive elements you are looking for.
Ryan Bierschenk (BOB member since 2009-01-26)
Good point! Thank you everyone for your quick responses.
w2s2b1 (BOB member since 2008-05-14)