Date Comparison Question

I have 9 datetime columns in a DataRow.
In a SELECT statement, I would like to have a Boolean return True if Date1
is later than every one of date2 thru date9, otherwise return False. Some
dates may be Null.
Is there a concise way to accomplish this?
TIA
Jav--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jav" wrote:

> I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False. Som
e
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||I don't think there is. If you need to do a lot of stuff with columns
then you can use dynamic SQL, but for just that check I think it's
going to be a very large case statement (perhaps use a udf to trim this
down). Is this required just by chance, or should you be looking at the
normalisation of your data?
when you start getting lots of columns like "expiryDate1, expiryDate2,
... expiryDate9" you might want to consider having a table Expirydates.
Cheers
Will|||A CASE statement is probably your best bet. It probably will not be very
concise, but it will work.
Keith Kratochvil
"Jav" <Jav@.discussions.microsoft.com> wrote in message
news:B8B9C1C7-165A-454D-ACEB-25CC24386979@.microsoft.com...
>I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False.
> Some
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||Thanks Will, Keith,
These are dates of certain events concerning a single entity represented by
the DataRow. The dates change frequently as those events occur in the life
of the entity, some dates changing more frequently than others. There are
tens of thousands of entities in the Table.
This is the only time I have needed to do the comparison. Would separating
the dates into their own table offer any performance benefit over a long Cas
e
statement? Since I use Guids as PK, it seems counterintuitive to separate a
n
8 byte field into a 24 plus byte DataRow and add 9 rows where there was one,
notwithstanding the normalization issues.
Thanks once again, I appreciate your help.
Jav
"Will" wrote:

> I don't think there is. If you need to do a lot of stuff with columns
> then you can use dynamic SQL, but for just that check I think it's
> going to be a very large case statement (perhaps use a udf to trim this
> down). Is this required just by chance, or should you be looking at the
> normalisation of your data?
> when you start getting lots of columns like "expiryDate1, expiryDate2,
> ... expiryDate9" you might want to consider having a table Expirydates.
> Cheers
> Will
>|||SQL doesn't have True or False, but you can adjust as required.
SELECT CASE WHEN date1 IS NULL THEN 'F'
WHEN date2 >= date1 THEN 'F'
WHEN date3 >= date1 THEN 'F'
WHEN date4 >= date1 THEN 'F'
WHEN date5 >= date1 THEN 'F'
WHEN date6 >= date1 THEN 'F'
WHEN date7 >= date1 THEN 'F'
WHEN date8 >= date1 THEN 'F'
WHEN date9 >= date1 THEN 'F'
ELSE 'T'
END
FROM Whatever
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
wrote:

>I have 9 datetime columns in a DataRow.
>In a SELECT statement, I would like to have a Boolean return True if Date1
>is later than every one of date2 thru date9, otherwise return False. Some
>dates may be Null.
>Is there a concise way to accomplish this?
>TIA
>Jav|||Thanks Roy,
I was just using True-False symbolically. I would return 1 or 0. Your CASE
statement example is very helpful. Thanks.
Jav
"Roy Harvey" wrote:

> SQL doesn't have True or False, but you can adjust as required.
> SELECT CASE WHEN date1 IS NULL THEN 'F'
> WHEN date2 >= date1 THEN 'F'
> WHEN date3 >= date1 THEN 'F'
> WHEN date4 >= date1 THEN 'F'
> WHEN date5 >= date1 THEN 'F'
> WHEN date6 >= date1 THEN 'F'
> WHEN date7 >= date1 THEN 'F'
> WHEN date8 >= date1 THEN 'F'
> WHEN date9 >= date1 THEN 'F'
> ELSE 'T'
> END
> FROM Whatever
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
> wrote:
>
>|||Performance of you database may be affected by normalising your data,
as you would then have to join on it. However performance is only one
aspect of a database, and scalability and maintainability are not to be
ignored either.
As to how it will affect the performance, one advantage of separating
it out into a separate table is that you can then index that one
column, providing a much faster search on your comparison. Also (and
this is just my own experience) I've noticed tables with a lot of
columns taking a lot longer to select, especially if you're often not
using all 9 cells, and just putting nulls in.
The best thing would be to try it out and see how it affects things
Cheers
Will|||Jav wrote:
> Would separating
> the dates into their own table offer any performance benefit over a long C
ase
> statement? Since I use Guids as PK, it seems counterintuitive to separate
an
> 8 byte field into a 24 plus byte DataRow and add 9 rows where there was on
e,
> notwithstanding the normalization issues.
This is the wrong question and is not the best way to approach design
problems. Start with a fully normalized design. Only compromise that if
you are forced to.
I'm not saying your design is or is not well normalized because I don't
know your data, but a large set of dates as columns in a table
intuitively looks like a weak design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--