October 18, 2010 at 7:55 am
Scenario is like - I have a table for product with records -
IDTimeStampsTeamStatus
12010-09-16 10:11:00.000TeamAWorking
22010-09-16 11:21:00.000TeamBReceived
32010-09-20 07:55:00.000TeamBAnalyzing
42010-09-20 15:15:00.000TeamBWorking
52010-09-27 11:12:00.000TeamAWorking
62010-09-27 11:12:20.000TeamBWorking
72010-09-27 14:34:00.000TeamAreceived
82010-10-08 13:34:00.000TeamAWorking
This is the structure when I sorted records on TimeStamps. I just want to retrieve records starting when certain product is currently in TeamA... e.g .in this case I want just two latest rows i.3. with ID 7 and 8 since product is with TeamA...
Anybody knows how??
Below are the script -
Create table #product(ID int, TimeStamps datetime, Team varchar(10),Status varchar(10));
Insert into #product(ID,TimeStamps,Team,Status) values(1,'2010-09-16 10:11:00.000','TeamA','Working')
Insert into #product(ID,TimeStamps,Team,Status) values(2,'2010-09-16 11:21:00.000','TeamB','Received')
Insert into #product(ID,TimeStamps,Team,Status) values(3,'2010-09-20 07:55:00.000','TeamB','Analyzing')
Insert into #product(ID,TimeStamps,Team,Status) values(4,'2010-09-20 15:15:00.000','TeamB','Working')
Insert into #product(ID,TimeStamps,Team,Status) values(5,'2010-09-27 11:12:00.000','TeamA','Working')
Insert into #product(ID,TimeStamps,Team,Status) values(6,'2010-09-27 11:12:20.000','TeamB','Working')
Insert into #product(ID,TimeStamps,Team,Status) values(7,'2010-09-27 14:34:00.000','TeamA','Received')
Insert into #product(ID,TimeStamps,Team,Status) values(8,'2010-10-08 13:34:00.000','TeamA','Working')
:rolleyes:
October 18, 2010 at 1:09 pm
If the sequence of ID column values start at 1 and are in the same order as the TimeStamps values then the following should work. The COALESCE is needed for those cases where there has been no change of teams.
SELECT ID FROM #product
WHERE ID > COALESCE((
SELECT MAX(ID) FROM #product
WHERE (Team <> (SELECT TOP 1 Team FROM #product ORDER BY ID DESC))
), 0)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply