Is There An Easier Way? Two Aggregate Fields From One Based On Dates

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.