June 17, 2008 at 6:10 am
I can best explain my Problem with a simplified example:
Given a Table like that:
ID,Status, date, firstname,lastname, result
--------------------------------------
1,B, 2008-05-01, Michael, Test, 1
2,B, 2008-05-15, Michael, Test, 1
3,K, null,Michael,Test,0
4,B,2008-04-14,Doris,Day,0
5,B,2008-05-12,Mike,Myers,1
6,K,null,Mike;Myers,0
7,B,2008-5-12,John,Doe,0
8,B,2008-5-13,John,Doe,1
"Status" identifies a Step in a Workflow Process, where "B" is always before "K" in the workflow, but I may have several "B" entries for a person. What I want to achieve, is that every first record in a persons process should habe a value of "0" in the result field, an a value of "1" in all subsequent steps. The problem is that I cannot simply sort my records by date, because all the "K" entries have a lower "date" than the "B" entries. In fact they are null.
So "result" should change to 0 in record no. 1, because it's the oldest entry for the process of Michael Test, and the "reslut" of record 3 should change to 1.
Record 4 should be left untouched because it's the only record for Doris Day.
"result"s of records no. 5 and six should again change
Records 7 and 8 are fine and should be left untouched
I know thsi is tricky, but that's why I cannot solve thsi on my own 😉
June 17, 2008 at 6:40 am
After making some test data myself (it would have been nice if you posted test data something like I have provided), this was pretty easy.
This assumes that the Status values will be in alphabetical order. It will also make NULL dates the earliest date - this was not that clear to me - if that is incorrect, you would have to do some modifications to make the NULL dates sort to the end.
[font="Courier New"]CREATE TABLE #MyTemp (ID INT, [Status] CHAR(1), [Date] DATETIME, FirstName VARCHAR(20), LastName VARCHAR(20), Result TINYINT)
GO
INSERT #MyTemp VALUES (1,'B','5/1/2008','Michael','Test',1)
INSERT #MyTemp VALUES (2,'B','5/15/2008','Michael','Test',1)
INSERT #MyTemp VALUES (3,'K',NULL,'Michael','Test',0)
INSERT #MyTemp VALUES (4,'B','4/14/2008','Doris','Day',0)
INSERT #MyTemp VALUES (5,'B','5/12/2008','Mike','Meyers',1)
INSERT #MyTemp VALUES (6,'K',NULL,'Mike','Meyers',0)
INSERT #MyTemp VALUES (7,'B','5/12/2008','John','Doe',0)
INSERT #MyTemp VALUES (8,'B','5/13/2008','John','Doe',1)
GO
; WITH Data (ID, [Status], [Date], FirstName, LastName, Result, RowNum)
AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY LastName, FirstName, [Status], [Date]) FROM #MyTemp
)
SELECT
ID, [Status], [Date], FirstName, LastName, Result
, CASE RowNum WHEN 1 THEN 0 ELSE 1 END AS NewResult
FROM
Data[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply