Need help with WHERE clause to exclude records

[elsenorjose]

I'm not a great SQL coder and I've been asked to modify a query to exclude records that meet the following conditions:

If the projected submission date is NULL
AND
the Actual Submitted date is not null and is before Dec 2010
OR
Actual Approved date is not null and is before Dec 2010

Can someone please help with the syntax as this has been driving me crazy for a few days now.

Thank you

 

[xxing]

Try something like this in the WHERE part of your sql statement

WHERE
(
ProjectedSubmissionDate is null and ActualSubmittedDate < to_date('1-Dec-10','dd-MON-yy')
)
or
(
ActualApprovedDate < to_date('1-Dec-10','dd-MON-yy')
);

regards
Mark

 

[elsenorjose]

Thanks Mark but won't that retrieve records that meet that criteria? I am trying to exclude records that meet that criteria.

Thanks again,
Jose

 

[xxing]

Hi Jose

Sorry I did not read your original emal properly. Try the reverse


WHERE
(
ProjectedSubmissionDate is not null and ActualSubmittedDate > to_date('1-Dec-10','dd-MON-yy')
)
or
(
ActualApprovedDate > to_date('1-Dec-10','dd-MON-yy')
);

If this does not work can I please see your sql statement

regards
Mark

 

[carp]

Just to be clear, are your requirements to exclude all records where

(the projected submission date is NULL AND the Actual Submitted date is not null and is before Dec 2010)
OR
(Actual Approved date is not null and is before Dec 2010)

or do you need

(the projected submission date is NULL)
AND 
((the Actual Submitted date is not null and is before Dec 2010) OR (Actual Approved date is not null and is before Dec 2010))

 

[elsenorjose]

Hi Carp,

The second statement is the correct one. Thank you for the clarifying question.

Mark, thanks for the suggestions. I'll see if I can tweak things a bit.

Thanks,
Jose

 

[carp]

In that case, I think the following WHERE clause should work:

WHERE
(projected_submission_date is NOT NULL)
OR
(TRUNC(NVL(Actual_Submitted_date, '1-DEC-2010')) >=  '1-DEC-2010') 
   AND 
  TRUNC(NVL(Actual_Approved_date,'1-DEC-2010')) >= '1-DEC-2010')

It's been a long day and I haven't tested this, but I'm pretty sure it's close to what you are looking for. If not, I'm sure somebody will point out the problem for us.

 

[sem]

I'd also recommend you to avoid NLS-specific format masks, as Dec is not the common name of 12-th month in any language ^)

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Dima - so good to see you again! It has been far too long.

 

[elsenorjose]

So, it's actually:

WHERE NOT
(
seq_status_schedule1.sched_date is NULL //Projected Submission
AND
(
seq_status_schedule2.actual_date is not null and seq_status_schedule2.actual_date < to_date('1-Dec-10','dd-MON-yy') //Actual Submitted
) 
OR 
(
seq_status_schedule3.actual_date is not null and seq_status_schedule3.actual_date < to_date('1-Dec-10','dd-MON-yy') //Actual Approved
)
)

What kept throwing me off is we DO want NULL submission dates, just not if they ALSO meet the other 2 criteria. So we couldn't use the following in the query because it effectively throws out all NULL Projected Submission Dates before evaluation the Actual Approved and Submitted dates.

WHERE
(projected_submission_date is NOT NULL)

Thank you Carp and Mark, though. Between what the two of you provided, I was able to walk through the problem and come up with the right solution.