Oracle CASE Statement

I know that the MDY() function within the SQL will fail … this tells Informix to return the Date value for the values sent.  Also CURRENT will need to be switched out to SYSDATE, but what will the SQL need to look like to correct the CASE statement below?

Name:
Nbr Closed this Qtr
Definition:
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 3
THEN
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(01,01,Year( CURRENT )-1) AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(03,31,Year( CURRENT )-1)
     THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(01,01,Year( CURRENT )) AND
      Sfhd_Loan_Activity.Date_Of_Closing <= MDY(03,31,Year( CURRENT ))
           THEN 1 ELSE 0 END )
END


TIA,
N
Nakuru1234Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
http:#35087493

should be correct

try it with your table and see, we can't test it, we don't have your data
0
 
slightwv (䄆 Netminder) Commented:
This is posted in an Oracle zone.  Is this Oracle or Informix?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
something like this, off the top of my head ...
CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM')) >= 1 AND TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) <= 3
THEN SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= TRUNC (TRUNC(SYSDATE, 'YYYY')-1 , 'YYYY') 
                                 AND Sfhd_Loan_Activity.Date_Of_Closing < ADD_MONTHS(3, TRUNC (TRUNC(SYSDATE, 'YYYY')-1 , 'YYYY'))
                       THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= TRUNC(SYSDATE, 'YYYY')  
                         AND Sfhd_Loan_Activity.Date_Of_Closing <= ADD_MONTHS(3,  TRUNC(SYSDATE, 'YYYY'))
           THEN 1 ELSE 0 END )
END

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
try this...

CASE
           WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'q')) = 1
           THEN
               SUM(
                   CASE
                       WHEN sfhd_loan_activity.date_of_closing >=
                                ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
                            AND sfhd_loan_activity.date_of_closing <=
                                    ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -9) - 1
                       THEN
                           1
                       ELSE
                           0
                   END)
           ELSE
               SUM(
                   CASE
                       WHEN sfhd_loan_activity.date_of_closing >= TRUNC(SYSDATE, 'yyyy')
                            AND sfhd_loan_activity.date_of_closing <=
                                    ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 3) - 1
                       THEN
                           1
                       ELSE
                           0
                   END)
       END

Open in new window

0
 
sdstuberCommented:
angeliii - your add_months parameters are backwards

also,  the end date is 3/31  so after adjusting by 3 months you to 4/1 you need to subtract 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are surely correct about the add_months parameters ...
but :
<= MDY(03,31,Year( CURRENT )-1)
means to me the same as
< 1/4/ ....
or am I wrong there?
0
 
sdstuberCommented:
MDY(03,31,Year( CURRENT )-1)


is equivalent to

to_date('03/31/' || to_char(sysdate,'yyyy')-1,'mm/dd/yyyy')

so

3/31/2010
0
 
sdstuberCommented:
ah,  I see what you're saying now

 I didn't notice you switched the <= to <

0
 
Nakuru1234Author Commented:
So can you send me the correct SQL revised...am a little confused with all the comments.

TIA,
N
0
 
sdstuberCommented:
if it doesn't work,  please provide sample data and expected output
0
 
Nakuru1234Author Commented:
When there is more data on the Oracle Instance I will try it and compare to Informix.  As of now it works for 1 qtr. Thanks for your help.

TIA,
N
0
 
Nakuru1234Author Commented:
Excellent answer and prompt help. Thanks.
0
All Courses

From novice to tech pro — start learning today.