January 12, 2015 at 8:29 am
I have a temp table I want to delete records from if the enterDate >arcDate (today's date) joined to a static table by acct. Like so:
temp table has id(int), acct(int), clientId(int)
static table has id(int), acct(int), clientId(int), enterDate(smalldatetime), processDate(smalldatetime)
BUT I need
1. to ignore(not delete) records that have an enterDate equal to either a conversiondate of '2010-01-01' OR a termDate of '2001-01-01'; If they do, use the processDate to select instead BUT
2. if the processDate is equal to the conversionDate or termdate then this record should be ignored (not deleted) as well
I created a select query with sub-selects but I'm not sure this is best:
declare @arcDate smalldatetime
set @arcDate = DATEADD(yy, -10, GETDATE())
declare @conversionDate smalldatetime, @termDate smalldatetime
set @conversionDate = cast(convert( varchar, '2010-01-01', 110 ) as smalldatetime )
set @termDate = cast(convert( varchar, '2001-01-01', 110 ) as smalldatetime )
select s.acct, s.enterDate, s.processDate from staticTable s
join temp tp on tp.acct = s.acct
where s.acct not in
(
select s.acct from staticTable s
join TEMP tp on tp.acct = s.acct
where s.enterDate = @conversionDate or s.enterDate = @termDate
and t.acct_id not in
(
select s.acct from staticTable s
join TEMP tp on tp.acct = s.acct
where s.processDate = @conversionDate or s.processDate = @termDate
)
)
and s.enterDate > @arcDate
***And the query doesn't successfully work as the exclusion records are still included.
January 12, 2015 at 9:30 am
SELECT tp.*, s.acct, s.enterDate, s.processDate
FROM temp tp
INNER join staticTable s
ON tp.acct = s.acct
WHERE
s.enterDate NOT IN (@conversiondate, @termDate)
OR -- if s.enterDate IS equal to @conversiondate or @termDate then
s.processDate NOT IN (@conversiondate, @termDate)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2015 at 9:35 am
1. to ignore(not delete) records that have an enterDate equal to either a conversiondate of '2010-01-01' OR a termDate of '2001-01-01'; If they do, use the processDate to select instead ...
What do you mean by "use the processDate to select instead"?
So you want to compare processDate with your @arcDate in this case?
If so, then this might work:
SELECT tp.* -- change this line to "DELETE tp" in order for this query to delete from "temp" table
FROM temp tp
JOIN staticTable s ON tp.acct = s.acct
WHERE s.processDate NOT IN (@conversionDate, @termDate) -- condition #2
AND (
(s.enterDate > @arcDate AND s.enterDate NOT IN (@conversionDate, @termDate))
OR (s.processDate > @arcDate AND s.enterDate IN (@conversionDate, @termDate))
)
January 12, 2015 at 10:08 am
"What do you mean by "use the processDate to select instead"?"
As in your response, compare EnterDate to the arcDate UNLESS the EnterDate = conversionDate or = termDate; in these instances compare to the processDate instead. Does that help? Sorry for the convolution.
January 12, 2015 at 10:09 am
I will try this. Seems more understandable the multiple case statement one of my colleagues suggested.
January 12, 2015 at 10:25 am
sclayton 889 (1/12/2015)
I will try this. Seems more understandable the multiple case statement one of my colleagues suggested.
The conditions are not really too complicated here and I would recommend them to be written using CASE WHEN.
It's irrelevant to this case, I guess, as you are about to delete from temp table, however, if the same would apply to permanent table, and you had indexes on the columns used in WHERE conditions, writing clauses using CASE WHEN would most likely turn this query to non-sargable (indexes would not be used).
January 12, 2015 at 10:36 am
Eugene Elutin (1/12/2015)
sclayton 889 (1/12/2015)
I will try this. Seems more understandable than the multiple case statement one of my colleagues suggested.The conditions are not really too complicated here and I would recommend them to be written using CASE WHEN.
It's irrelevant to this case, I guess, as you are about to delete from temp table, however, if the same would apply to permanent table, and you had indexes on the columns used in WHERE conditions, writing clauses using CASE WHEN would most likely turn this query to non-sargable (indexes would not be used).
Many Thanks for the note on:
if the same would apply to permanent table, and you had indexes on the columns used in WHERE conditions, writing clauses using CASE WHEN would most likely turn this query to non-sargable (indexes would not be used).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply