Stumped by SQL challenge

Here is the table:

CREATE TABLE [child]
(
[pk_child_id] [int] NOT NULL ,
[fk_parent_id] [int] NOT NULL ,
[code] [char] (2)NOT NULL ,
[dt] [datetime] NOT NULL ,
[newcode] [int] NULL
)

There is a situation where there will be more than one record with the
same [fk_parent_id] value, but different values for the [code]field.
If one of those records has a [code]= 5, but the [dt] is AFTER a
similar record where [code]= 6 or [code]= 7 (but same [fk_parent
_id]
value), I need to set [newcode] = 10.  How can I pull this off?  Again,
the group of records can have different [code] values, different [dt
]
values, but a common [fk_parent_id].

Help!

Thanks for posting the DDL. It does help if you include keys and constraints
with your CREATE TABLE statements and post some sample data abd required
results too. Here's my guess, hopefully it will give you a start even if it
isn't exactly what's required.

UPDATE child
SET newcode = 10
WHERE code = 5
AND EXISTS
(SELECT *
FROM child AS C
WHERE fk_parent_id = child.fk_parent_id
AND code > child.code
AND dt < child.dt) ;

--
David Portas
SQL Server MVP
--

Thank you for the information.  I'm wondering how this would work as a
SELECT statement.  Also, let's assume there is a foreign key constraint
on fk_parent_id.  And the pk field is a primary key.

David Portas  wrote:
> Thanks for posting the DDL. It does help if you include keys and constrain
ts
> with your CREATE TABLE statements and post some sample data abd required
> results too. Here's my guess, hopefully it will give you a start even if i
t
> isn't exactly what's required.
>
> UPDATE child
>  SET newcode = 10
>  WHERE code = 5
>   AND EXISTS
>   (SELECT *
>    FROM child AS C
>    WHERE fk_parent_id = child.fk_parent_id
>     AND code > child.code
>     AND dt < child.dt) ;
>
> --
> David Portas
> SQL Server MVP
> --

On 20 Sep 2005 09:10:49 -0700,  imani_technology_spa
m@yahoo.com wrote:

>Thank you for the information.  I'm wondering how this would work as a
>SELECT statement.  Also, let's assume there is a foreign key constraint
>on fk_parent_id.  And the pk field is a primary key.

Hi imani,

A better way to get replies is to post your complete table structure,
including keys and relationships, as DDL. Also, supply sample data as
INSERT statements and expected output.

Here's a wild guess:

SELECT other columns,
CASE
WHEN code = 5
AND EXISTS
(SELECT *
FROM   child AS C
WHERE fk_parent_id = child.fk_parent_id
AND code > child.code
AND dt < child.dt)
THEN 10
ELSE newcode
END AS newcode
FROM  child

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Please post completeDDL, so that people do not have to guess what the
keys, constraints, Declarative Referential Integrity, data types, etc.
in your schema are. Sample data is also a good idea, along with clear
specifications.

We never use prefixes like "pk-" or 'fk-" on data element names
(ISO-11179 rules).  We also know that columns are not fields and rows
are not records. Names like "dt" and "code" beg that question "of
what??"  Since you used a singular name for the table, does it have
only one row?

It looks like you are trying to build a self-referencing adjacency
list model for a hierarchy, but you never gave us constraints for that.
There are better ways to build a hierarchies.  Can you explain your
problem?