declare @T table(ID int, Permit# INT, LocationID varchar(20), WorkType varchar(20))
insert @T
values 
(1, 349, '1a', 'Add room'),
(2, 350, '2c', 'replace windows'),
(3, 351, '1a', 'install footers'),
(4, 352, '456c', 'new construction'),
(5, 353, '456c', 'electric')

select 
    *,
    dense_rank() over (order by LocationID) as RowRank
from @T

ALTER TRIGGER Work_Trigger1
ON dbo.[Work]
FOR INSERT, UPDATE
AS
IF UPDATE (LocationID)
begin
	--clear RelatedIDs of rows which changed
	UPDATE w
	SET w.RelatedID = null
	FROM [Work] w
	WHERE w.ID in (SELECT i.ID FROM inserted i
					INNER JOIN deleted d
						ON i.ID = d.ID AND i.LocationID <> d.LocationID)

	--set realted id's		
	UPDATE w
	SET w.RelatedID = (SELECT top 1 isnull(w2.RelatedID, w2.ID)
						FROM [Work] w2
						WHERE w2.LocationID = w.LocationID
						ORDER BY w2.RelatedID desc, w.ID)
	FROM [Work] w
	WHERE w.RelatedID is null
		AND w.ID IN (SELECT ID FROM inserted)
end

quote:

---

Originally posted by TimSman

When does the email go out vs. when does the data get entered? In other words, if I enter a work order for a room at 0800, and another at 1500, when did the email for one (or both) get sent?

Couldn't you just group by the LoactionID?

---