Fanbladeus


When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose.

I have a table like this:

Balance Date
1 200 2/14/2000
2 350 2/14/2000
3 32 2/14/2000
2 723 2/14/1998
3 354 2/14/1998
1 321 2/14/2000
2 673 2/14/1998
3 581 2/14/2000
2 574 2/14/1998
3 50 2/14/2000
1 10 2/14/2000

And essentially need this.

Total Balance Before 1/1/2000 Total Balance After 1/1/2000
1 0 531
2 1970 350
3 354 663

Right now I'm splitting it into two temp tables and then joining them together.

Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria.

Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in.

Utimately this will be used in a SPROC for a Multivalued SSRS report.



Re: Is there an easier way? Two aggregate Fields from one based on dates

Kent Waldrop Oc07


You can use CASE statements to partition the data as you need it. An example of this might look something like:

Code Block

declare @xample table
( id integer,
Balance integer,
Date datetime
)

declare @splitDate datetime
set @splitDate = '1/1/2000'

insert into @xample
select 1, 200, '2/14/2000' union all
select 2, 350, '2/14/2000' union all
select 3, 32, '2/14/2000' union all
select 2, 723, '2/14/1998' union all
select 3, 354, '2/14/1998' union all

select 1, 321, '2/14/2000' union all
select 2, 673, '2/14/1998' union all
select 3, 581, '2/14/2000' union all
select 2, 574, '2/14/1998' union all
select 3, 50, '2/14/2000' union all
select 1, 10, '2/14/2000'

select id,
sum
( case when Date < @splitDate
then Balance else 0
end ) as [Before Balance],
sum
( case when Date >= @splitDate
then Balance else 0
end) as [After Balance]
from @xample
group by ID

/* -------- Sample Output: --------
id Before Balance After Balance
----------- -------------- -------------
1 0 531
2 1970 350
3 354 663
*/






Re: Is there an easier way? Two aggregate Fields from one based on dates

Fanbladeus

Awesome! Thanks so much.

I read your initial post about using a Case to segment out the data and my mind went PIIIINNG.

THANKS!

Smile