October 18, 2010 at 7:55 am
Scenario is like - I have a table for product with records -
ID TimeStamps Team Status
1 2010-09-16 10:11:00.000 TeamA Working
2 2010-09-16 11:21:00.000 TeamB Received
3 2010-09-20 07:55:00.000 TeamB Analyzing
4 2010-09-20 15:15:00.000 TeamB Working
5 2010-09-27 11:12:00.000 TeamA Working
6 2010-09-27 11:12:20.000 TeamB Working
7 2010-09-27 14:34:00.000 TeamA received
8 2010-10-08 13:34:00.000 TeamA Working
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