I have a web page where users can change information about themselves
and submitted to a database.  The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away.  The user
can then go in again and make further changes thus creating another
record in the UserChanges table.  Once a day these changes are brought
down to our main database.  I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day.  The problem is that one of the fields is a bit field
that indicates if the email address was changed or not.  This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example

ID  PersonID  EmailAddress      EmailChange   Downloaded
1    200       t***@test.com     False         False
2    200       bl***@blank.com   True          False
3    200       bl***@blank.com   False         False


What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.

Is this possible to do with out using a cursor?

A trigger should be able to do it:

create trigger tri_UserChanges on UserChanges after insert
as
if @@rowcount = 0
    return

update u
set
    EmailChange = 1
from
        UserChanges    u
join   inserted           i on i.ID = u.ID
where exists
(
    select
        *
    from
        UserChanges u2
    where
            u2.PersonID    = i.PersonID
    and   u2.ID             <> i.ID
    and   u2.EmailChange = 1
    and   u2.Doenloaded   = 0
)

I have a web page where users can change information about themselves
and submitted to a database.  The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away.  The user
can then go in again and make further changes thus creating another
record in the UserChanges table.  Once a day these changes are brought
down to our main database.  I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day.  The problem is that one of the fields is a bit field
that indicates if the email address was changed or not.  This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example

ID  PersonID  EmailAddress      EmailChange   Downloaded
1    200       t***@test.com     False         False
2    200       bl***@blank.com   True          False
3    200       bl***@blank.com   False         False


What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.

Is this possible to do with out using a cursor?

Just as a side note, you may want to reconsider your design.  As you
can obviously see, a bit field doesn't really tell you much :)  I'm
also assuming that "previous" has meaning to you, because a rows in a
table don't really have an order to them.

Here's a quick-and-dirty stab at it:

DECLARE @s TABLE (ID int, PersonID int, EmailAddress varchar(20),
            EmailChange bit, Downloaded bit)

INSERT INTO @s
SELECT 1, 200, 't***@test.com', 0, 0
UNION ALL
SELECT 2, 200, 'bl***@blank.com', 1, 0
UNION ALL
SELECT 3, 200, 'bl***@blank.com', 0, 0
UNION ALL
SELECT 4, 500, 't***@test.com', 0, 0
UNION ALL
SELECT 5, 500, 'bl***@blank.com', 1, 0
UNION ALL
SELECT 6, 500, 'bl***@blank.com', 0, 1


SELECT *
FROM @s

SELECT DISTINCT s.ID, s.PersonID, s.EmailAddress,
    EmailChange = COALESCE(s2.EmailChange, s.EmailChange),
    s.Downloaded
FROM @s s LEFT JOIN @s s2 ON s.PersonID = s2.PersonID
                AND s.ID > s2.ID
                AND s2.EmailChange = 1
                AND s2.Downloaded = 0
WHERE s.ID IN (SELECT MAX(ID)
        FROM @s
        WHERE Downloaded=0
        GROUP BY PersonID)


HTH,
Stu

rocky20 wrote:
Show quoteHide quoteIt doesn't seem like you'll need a cursor to do this. I'm not clear
about a few things though. Firstly what is/are the keys in this table?
Secondly how do we know which row is the latest? Don't use an IDENTITY
column to track the latest row. Add a DATETIME column to do that.

Finally, what's the point of the EmailChange column? It looks redundant
to me, given that you preserve the history of the email values anyway.
I think you should drop EmailChange.

Assuming you have a column to indicate the date and time of the change
you can retrieve the latest version like this:

/* Get the new email address only where the
latest version hasn't been downloaded */
SELECT emailaddress
FROM UserChanges AS U
WHERE changed_datetime =
  (SELECT MAX(changed_datetime)
   FROM UserChanges
   WHERE personid = U.personid
   HAVING MAX(changed_datetime) =
    MAX(CASE WHEN downloaded = 'False' THEN changed_datetime END));
  AND downloaded = 'False' /* should be 0? */

(untested)

Please post DDL, 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.  It is very hard to debug code when you do not let us
see it.

Doesn't that hit you as "a bit" redundant?  (sorry, had to do the pun)
Just over-write the old email with the new one, based on a timestamp

One of your major problems is that you do not know that fields and
records are not part of SQL; columns and rows are different creatures.


When someone logs into the routine.  check to see if they have data in
the working tables.  If not, copy all the old data over to your working
table.  Let the user UPDATE the working data on a column by column
basis in the working database.  Or if you really need to keep every
change, then add that timestamp to get the last copy.

Then do your data scrubbing and replace the old data with the new.  I
would guess that a VIEW with INSTEAD OF TRIGGERs would help.

No cursors needed.

Toughy Normalization Problem
Timeout expired - on a simple SqlDataReader:Read() call
Primary Key Question
Is it possible to join two tables in a View/Query where the Second Table is based on a Name in the F
OBJECTPROPERTY problem