Retreive min date when status changes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retreive min date when status changes

Author

Topic

wilkid
Starting Member

1 Post

Posted - 2010-09-24 : 10:41:59

Hi,

I'm having some trouble getting a query just the way I need it and am looking for some help.

The data in the table XYZ looks like this:

Date Status
01/01/2010 A
02/01/2010 A
03/01/2010 A
04/01/2010 P
04/05/2010 P
05/10/2010 P
06/07/2010 A
07/30/2010 A

The result set that I'm looking to get from my query should look like this:

Date Status
01/01/2010 A
04/01/2010 P
06/07/2010 A

My current query looks like:

Select min(Date) as Date, Status
From XYZ
Group by Status

And returns the result set:

Date Status
01/01/2010 A
04/01/2010 P

But doesn't return the fact that the status changed back to A on 06/07/2010.

Any help will be greatly appreciated.

Thanks in advance.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-24 : 12:18:40

Here is your query:


CREATE TABLE #temp_1
(
id INT IDENTITY(1,1),
Date Datetime,
Status VARCHAR(50)
)
CREATE TABLE #temp_2
(
id INT IDENTITY(1,1),
Date DATETIME,
Status VARCHAR(50),
)

INSERT INTO #temp_1
SELECT Date,Status FROM tbl_XYZ
INSERT INTO #temp_2
SELECT Date,Status FROM tbl_XYZ

 SELECT * FROM #temp_1 
        WHERE id NOT IN (
				SELECT B.id  
                                        FROM #temp_1 A 
			                INNER JOIN #temp_2 B 
					        ON A.id + 1 = B.id AND A.Status = B.Status                  
                                                    AND   A.Date < B.date 
                         )

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-24 : 22:14:16

Try this ...

declare @temp table(a datetime, b char(1))
insert into @temp values('01/01/2010', 'A')
insert into @temp values('02/01/2010', 'A')
insert into @temp values('03/01/2010', 'A')
insert into @temp values('04/01/2010', 'P')
insert into @temp values('04/05/2010', 'P')
insert into @temp values('05/10/2010', 'P')
insert into @temp values('06/07/2010', 'A')
insert into @temp values('07/30/2010', 'A')
select * from @temp

;with temp as (select *, ROW_NUMBER() over(order by a) as rn from @temp)
select convert(varchar(10), t2.a,101) as a, t2.b from temp t1 join temp t2
on (t1.b!=t2.b and t1.rn=t2.rn-1) or (t1.rn=1 and t2.rn=1)

--result--
a b
---------- ----
01/01/2010 A
04/01/2010 P
06/07/2010 A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:31:22

[code]
SELECT t.*
FROM YourTable t
OUTER APPLY(SELECT TOP 1 Status
FROM YourTable
WHERE Date < t.Date
ORDER BY Date DESC)t1
WHERE t1.Status <> t.Status
OR t1.Status IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-09-25 : 04:12:03

One from me


insert into @temp values('01/01/2010', 'A')
insert into @temp values('02/01/2010', 'A')
insert into @temp values('03/01/2010', 'A')
insert into @temp values('04/01/2010', 'P')
insert into @temp values('04/05/2010', 'P')
insert into @temp values('05/10/2010', 'P')
insert into @temp values('06/07/2010', 'A')
insert into @temp values('07/30/2010', 'A')

select b,min(a)dt from
(
select a,b,dense_rank()over(partition by b order by a)-row_number()over(order by a)rid from @temp
)t group by b,rid order by min(a)

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources