×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL- find records that are NOT the first or the last day of the month

SQL- find records that are NOT the first or the last day of the month

SQL- find records that are NOT the first or the last day of the month

(OP)
SQL script to find and delete records that are not the
first or the last day of the month.  It correctly returns
records with dates prior to 01 Jan 08.  However, it is
returning first day of month and last day of month records.
(see attached)
 

RE: SQL- find records that are NOT the first or the last day of the month

(OP)
This forum can't upload attachments so here's the SQL;

--Find all Risk valuations that are not the first or last day of the month:
SELECT * FROM valuation
  WHERE 1 = 1
  AND valuationtime < '01jan08'
  AND (To_Char(valuationtime, 'DD') != '01'  
       OR To_Char((valuationtime + 1), 'DD') != '01' )
  AND valuationmode IN ('Gas Valuation All Products', 'Power Valuation All Products');  --16SEP2008 C.H.

COMMIT;  --16SEP2008 C.H.

--Report number of rows to be deleted in valuationdetail from above list of valuations:
SELECT Count(*) FROM valuationdetail WHERE valuation IN
  (SELECT valuation FROM valuation
  WHERE 1 = 1
  AND valuationtime < '01jan08' -- only delete before this date
  AND (To_Char(valuationtime, 'DD') != '01'  -- not the first day of the month
       OR To_Char((valuationtime + 1), 'DD') != '01' --not the last day of the month
      )
  AND valuationmode IN ('Gas Valuation All Products', 'Power Valuation All Products') --valuation mode restiction
  );

COMMIT;  --16SEP2008 C.H.

--Delete all Risk valuations that are not the first or last day of the month:
--  must run in order as below:
DELETE FROM valuationdetail WHERE valuation IN
  (SELECT valuation FROM valuation
  WHERE 1 = 1
  AND valuationtime < '01jan08' -- only delete before this date
  AND (To_Char(valuationtime, 'DD') != '01'  -- not the first day of the month
       OR To_Char((valuationtime + 1), 'DD') != '01' --not the last day of the month
      )
  AND valuationmode IN ('Gas Valuation All Products', 'Power Valuation All Products') --valuation mode restiction
  );

COMMIT;  --16SEP2008 C.H.

DELETE FROM valuation
  WHERE 1 = 1
  AND valuationtime < '01jan08' -- only delete before this date
  AND (To_Char(valuationtime, 'DD') != '01'  -- not the first day of the month
       OR To_Char((valuationtime + 1), 'DD') != '01' --not the last day of the month
      )
  AND valuationmode IN ('Gas Valuation All Products', 'Power Valuation All Products');

COMMIT;
 

RE: SQL- find records that are NOT the first or the last day of the month

charoian, please post your next question in the oracle forum, not the ANSI SQL forum

ta very much

smile

r937.com | rudy.ca

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close