Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL- find records that are NOT the first or the last day of the month
- Thread starter charoian
- Start date
- Thread starter
- #2
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;
--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;
- Status
Similar threads
- Locked
- Question
