November 7, 2002 at 3:19 pm
I need to narrow down a set of data based on the greater of 2 dates that are stored in 2 separate fields. Any suggestions on how I could accomplish this through a simple select statement?
November 7, 2002 at 3:26 pm
You could use a case statement to select the greater of the dates into a single field in a correlated subquery, then criterialize in the outer query's where clause.
If you'll give your table structure and what your trying to return in your dataset, I'm sure someone (if not me) will write you a sample...
November 7, 2002 at 3:51 pm
Create table case(
case_number varchar(20),
open_date datetime,
closed_date datetime,
ra_date datetime)
Need to select all datarows where the greater of the closed_date and the ra_date is greater than a inputed parameter
November 7, 2002 at 4:08 pm
SELECT * FROM [case] WHERE
CASE
WHEN closed_date > ra_date THEN closed_date
ELSE ra_date
END > @input
November 7, 2002 at 4:40 pm
That works well. Thanks.
Now when I add it to the actual query (I'd used a simplified query in my example), I get an error "The table 'Table_B' is an inner member of an outer-join clause...."
Here is the query that gives that error:
SELECT A.case_number, A.case_log_opnd_date, B.updated_timestamp
FROM Table_A A, Table_B B
WHERE B.case_number =* A.case_number
AND CASE WHEN A.case_log_clsd_date > B.updated_timestamp
THEN A.case_log_clsd_date
ELSE B.updated_timestamp
END > '10-09-2002'
AND CASE WHEN A.case_log_clsd_date > B.updated_timestamp
THEN A.case_log_clsd_date
ELSE B.updated_timestamp
END > '10-15-2002'
AND ( ( A.source_type = 'C' ) OR ( A.source_type = 'P' ) )
November 7, 2002 at 5:20 pm
Try using the RIGHT JOIN and ON syntax, it occurrs before the WHERE clause is done, like this (notes in query)
SELECT A.case_number, A.case_log_opnd_date, B.updated_timestamp
FROM Table_A A
RIGHT JOIN Table_B B
ON
B.case_number = A.case_number
WHERE
--Don't understand why you use this twice
CASE WHEN A.case_log_clsd_date > B.updated_timestamp
THEN A.case_log_clsd_date
ELSE B.updated_timestamp
END > '10-09-2002'
-- doing this twice will mean all records > 10-15-2002 will be the result, did you mean a between or < 10-15-2002
AND CASE WHEN A.case_log_clsd_date > B.updated_timestamp
THEN A.case_log_clsd_date
ELSE B.updated_timestamp
END > '10-15-2002'
AND A.source_type IN ('C','P') -- in this case it is better to use IN instead or a bunch of ORs, some performance gains and easier to read.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply