A little SQL problem I'm having –
I want to update the MaxInd and MinInd columns of a table to indicate which
rows have minimum or maximum event date for a given URN.
It is important that only one record per URN be marked as the Maximum or
Minimum but it is possible that a single event may be both the maximum AND
the minimum
This problem is quite simple to solve if event dates are all unique;
problems start to occur when multiple minimum or maximum dates exist in this
list
Consider the table below which for the moment has no ‘problem’ entries
Tables
Event
ID URN EventDate MaxInd MinInd
-- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
Values for the MaxInd and MinInd columns where supplied using the following
update
UPDATE Event
SET
MaxInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END,
MinInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END
FROM (
SELECT MAX(EventDate) AS MaxDate, MIN(EventDate) AS MinDate, URN
FROM Event
GROUP BY URN
) AS Ranges
WHERE Event.URN = Ranges.URN
Now lets update the Event table to introduce some problem events and the
values for MaxInd and MinInd I would like –
ID URN EventDate MaxInd MinInd Notes
-- -- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
7 1 01/01/2006 N N Same event date
as ID 1
8 2 08/01/2006 N N Same event date
as ID 4
9 3 08/01/2006 N N Same event date
as ID 6
When using the SQL presented above, this data scenario will result in the
three new records being marked with a MaxInd or MinInd in addition to their
related events also being marked as MinInd or MaxInd.
This situate breaks the rule that only one event per URN can be either the
Maximum or MinimumStephen wrote:
> This situate breaks the rule that only one event per URN can be either the
> Maximum or Minimum
... and therefore your requirements specification is incomplete. You
said you only want to update one row but you haven't told us which one
it should be. SQL isn't good at stuff like "Give me any one row. I
don't care which". Even if it were easier solve, code that returns
random results tends to look like a bug to end users and testers. For
that reason, you should always query an ambiguity like this one by
going back to the user, business owner or whoever.
Also, please post a CREATE TABLE statement for the table. That way we
don't have to guess what the actual datatypes and constraints are and
we can test out possible solutions for you. Don't forget to include
keys. Presumably you do have some keys to work with? I don't like to
make too many assumptions without seeing the table structure but on the
face of it the table design looks weak. I'd suggest you eliminate the
duplicate data rather than write lots of complex code to cope with it.
Finally, do tell us what version of SQL Server you are using.
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
--|||Also, the maximum and minimum should be functions of the data in your table,
and not stored as data themselves. They are results of a query and should
be determined when you need to access them, not stored and physically
updated. Of course this is a rule of database design, and sometimes there
are reasons (i.e. performance) for breaking these rules. Usually, however,
you can follow the rules and still get good performance, if you are careful
with how you write your SQL queries and how you index your columns.
You should consider either creating a unique constraint that prevents these
duplicate dates, or adding a sequence column to differentiate them.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143818532.404505.236730@.i40g2000cwc.googlegroups.com...
> Stephen wrote:
the
> ... and therefore your requirements specification is incomplete. You
> said you only want to update one row but you haven't told us which one
> it should be. SQL isn't good at stuff like "Give me any one row. I
> don't care which". Even if it were easier solve, code that returns
> random results tends to look like a bug to end users and testers. For
> that reason, you should always query an ambiguity like this one by
> going back to the user, business owner or whoever.
> Also, please post a CREATE TABLE statement for the table. That way we
> don't have to guess what the actual datatypes and constraints are and
> we can test out possible solutions for you. Don't forget to include
> keys. Presumably you do have some keys to work with? I don't like to
> make too many assumptions without seeing the table structure but on the
> face of it the table design looks weak. I'd suggest you eliminate the
> duplicate data rather than write lots of complex code to cope with it.
> Finally, do tell us what version of SQL Server you are using.
> --
> 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
> --
>
