Query Help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-26 : 21:00:22

Ok, I am having a T-SQL Block...

Seemingly simple thing to do, but I can't figure it out for some reason:

Data:

CREATE TABLE #AccountData(
AccountNo INT,
PostDate Date,
Status Varchar(6))

INSERT INTO #AccountData VALUES(1, '1/31/2012', 'Closed')
INSERT INTO #AccountData VALUES(1, '12/31/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '11/30/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '10/31/2011', 'Closed')
INSERT INTO #AccountData VALUES(1, '9/30/2011', 'Closed')
INSERT INTO #AccountData VALUES(1, '8/31/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '7/31/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '6/30/2011', 'Closed')
INSERT INTO #AccountData VALUES(1, '5/31/2011', 'Closed')
INSERT INTO #AccountData VALUES(1, '4/30/2011', 'Closed')
INSERT INTO #AccountData VALUES(1, '3/31/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '2/28/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '1/31/2011', 'Open')
INSERT INTO #AccountData VALUES(1, '12/31/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '11/30/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '10/31/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '9/30/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '8/31/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '7/31/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '6/30/2010', 'Open')
INSERT INTO #AccountData VALUES(1, '5/31/2010', 'Open')
INSERT INTO #AccountData VALUES(2, '1/31/2012', 'Closed')
INSERT INTO #AccountData VALUES(2, '12/31/2010', 'Open')
INSERT INTO #AccountData VALUES(2, '11/30/2010', 'Open')
INSERT INTO #AccountData VALUES(2, '10/31/2010', 'Open')

I basically want for each Account, the earliest Record (PostDate) And the Record Each Time the Status Changes.

So:
1, 5/1/2010, Open
1, 4/30/2011, Closed
1, 7/31/2011, Open
1, 9/30/2011, Closed
1, 11/30/2011, Open
1, 1/31/2011, Closed
2, 10/31/2010, Open
2, 1/31/2012, Closed

Thanks,

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 23:34:22

[code]
SELECT a.*
FROM AccountData a
OUTER APPLY (SELECT TOP 1 Status
FROM AccountData
WHERE AccountNo = a.AccountNo
AND PostDate < a.PostDate
ORDER BY PostDate DESC
)b
WHERE a.Status <> COALESCE(b.Status,'')
[/code]

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-26 : 23:47:09

Ok, I tried to simplify a much more difficult issue, and unfortunately, I over simplified it....I actually need the Date the status changed too. This meets the requirement I gave, but I actually also need the date the status changed too, so:

1, 5/1/2010, 4/30/2011, Open
1, 4/30/2011, 7/31/2011, Closed
1, 7/31/2011, 9/30/2011, Open
1, 9/30/2011, 11/30/2011, Closed
1, 11/30/2011, 1/31/2011, Open
1, 1/31/2011, NULL, Closed
2, 10/31/2010, 1/31/2012, Open
2, 1/31/2012, NULL, Closed

Sorry, I thought the original scenario I gave was accurate..

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 23:58:38

[code]
SELECT *
FROM AccountData a
OUTER APPLY (SELECT TOP 1 PostDate AS PrevStatusDate,Status AS PrevStatus
FROM AccountData
WHERE AccountNo = a.AccountNo
AND PostDate < a.PostDate
ORDER BY PostDate DESC
)b
WHERE a.Status <> COALESCE(b.Status,'')
[/code]

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-27 : 01:01:29

That doesn't return the correct Date (PrevStatusDate). It gets me pretty close. I'll see if I can get there.

Thanks for your help,

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-27 : 01:21:50

I got it, thanks for getting me on the right track Visakh, I was trying to use some silly Recursive CTE, and coming up completely wrong.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 21:19:29

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources