Trouble with data table and date range 2 1

[dinzana]

This is very much a continuation of a previous thread (thread183-1365652), I am so sorry, but I am stuck. I now have a table with the below data and I am trying to get the resultset listed beneath. The previous thread did not have accountId in the mix, so it gets a bit more complicated. You have given me so many great replies and ideas, and I once again, I thank you in advance.


Table:

declare @TEST table(accountId int, startDate char(10), endDate char(10), amt float)
insert into @TEST
select 81987,'02/02/2007', '04/02/2007', -30.9055
union select 81987,'02/05/2007', '04/02/2007', -82.4146
union select 81987,'02/06/2007', '04/02/2007', -30.396
union select 90000,'02/02/2007', '04/02/2007', -40.00
union select 90000,'02/05/2007', '04/02/2007', -50.00
union select 90000,'02/06/2007', '04/02/2007', -60.00






ResultSet

accountId startDate endDate amt
----------------------------------------------------------
81987 02/02/2007 02/05/2007 -30.9055
81987 02/05/2007 02/06/2007 -113.3201
81987 02/06/2007 04/02/2007 -143.7161
90000 02/02/2007 02/05/2007 -40
90000 02/05/2007 02/06/2007 -90
90000 02/06/2007 04/02/2007 -150

 

[sunila7]

How about this query.

select * from @TEST
select accountID, startdate,enddate,
(SELECT SUM(amt) FROM @TEST T WHERE T.AccountID = T1.AccountID and T.StartDate<=T1.StartDate) amt
FROM
@TEST T1
ORDER BY AccountID,StartDate

Sunil

 

[dinzana]

Sunil, thanks so much, that is close, but the endDates in the resultset are all 04/02/2007. I need them to be the startDate of the next record. See what I mean?

Thanks so much for looking at this.

-dinzana

 

[dinzana]

GOT IT!

Thanks to you Sunil and thanks to MonkSnake for all of your help!

select accountID, startDate,
isnull((select min(startDate) from @tTEST where startDate > T1.startDate and T1.endDate = endDate), endDate) as endDate,
(SELECT SUM(amt) FROM @TEST T WHERE T.AccountID = T1.AccountID and T.StartDate<=T1.StartDate) amt
FROM
@TEST T1
ORDER BY AccountID,StartDate