Trouble with a data table and date range 2

[dinzana]

Here is a sample of the source data table


GotIt
startDt endDt Amt
01/01/2007 04/04/2007 100
02/02/2007 04/04/2007 150
03/03/2007 04/04/2007 250

I am trying to get the data into a temp table to show the date range and amount represented in that range as shown below. Any ideas would be greatly appreciated.

Need it
startDt endDt Amt
01/01/2007 02/02/2007 100
02/02/2007 03/03/2007 250
03/03/2007 04/04/2007 400

 

[monksnake]

Will the data always follow that same format as it is in GotIt, where the endDt will always be the greatest date in the table?

<.

 

[dinzana]

Thanks for taking a look. To answer your question, no the endDt can have other dates.

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (StartDt [COLOR=#FF00FF]datetime[/color], EndDate [COLOR=#FF00FF]datetime[/color], Amt [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'01/01/2007'[/color],[COLOR=red]'04/04/2007'[/color],100)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'02/02/2007'[/color],[COLOR=red]'04/04/2007'[/color],150)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'03/03/2007'[/color],[COLOR=red]'04/04/2007'[/color],250)

[COLOR=blue]DECLARE[/color] @Test1 [COLOR=blue]TABLE[/color] (StartDt [COLOR=#FF00FF]datetime[/color], EndDate [COLOR=#FF00FF]datetime[/color], Amt [COLOR=blue]int[/color], Id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test1
[COLOR=blue]SELECT[/color] StartDt, EndDate, Amt
       [COLOR=blue]FROM[/color] @Test
       [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] StartDt

[COLOR=blue]SELECT[/color] Test1.StartDt,
       [COLOR=#FF00FF]COALESCE[/color](Test2.StartDt,Test1.EndDate) [COLOR=blue]AS[/color] EndDate,
       Test1.Amt+[COLOR=#FF00FF]COALESCE[/color](Test2.Amt,0) [COLOR=blue]AS[/color] Amt
[COLOR=blue]FROM[/color] @Test1 Test1
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Test1 Test2 [COLOR=blue]ON[/color] (Test1.Id-1) = Test2.Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

Well, here is a very poorly written example that will get you the desired results.

declare @table table(startDate char(10), endDate char(10), amt int)
insert into @table
select       '01/01/2007',  '04/04/2007',      100
union select '02/02/2007',  '04/04/2007',      150
union select '03/03/2007',  '04/04/2007',      250
union select '01/03/2007',  '04/07/2007',      100
union select '02/13/2007',  '04/07/2007',      230

select a.startDate, 
isnull((select min(startDate) from @table where startDate > a.startDate and a.endDate = endDate), endDate) endDte,
(select sum(amt) from @table where endDate = a.endDate and startDate < isnull((select min(startDate) from @table where startDate > a.startDate and a.endDate = endDate), a.endDate)) from @table a order by a.endDate, a.startDate

I know it can be cleaned up, I'll see if I actually know how to clean it up.

<.

 

[dinzana]

Thanks to you both for your input, great ideas and I was able to get to what I needed with adjustments! You are the greatest! I hope some day to reciprocate

 

[dinzana]

If I were to have an accountId included in this, how would I incorportate the same logic? for instance:

declare @table table(accountId, startDate char(10), endDate char(10), amt int)
insert into @table
select 123, '01/01/2007', '04/04/2007', 100
union select 123, '02/02/2007', '04/04/2007', 150
union select 123, '03/03/2007', '04/04/2007', 250
union select 456, '01/03/2007', '04/07/2007', 100
union select 456, '02/13/2007', '04/07/2007', 230

Thanks so much!