How to write an efficient select statement

I am trying to write a select statement that will write only credit balance records by week.
Example:

TempReserve
BillNO   Type       Date              Amt             OutstandingBal
1286      INV     1/10/2012    100.00                         100.00
1286     Adj       2/3/2012       <10.00>                        90.00
1286     Cash    3/14/2012    <100.00>                      -10.00
1286      Cash   3/16/2012    <100.00>                    -110.00  
1286     Adj       4/30/2012    110.00                                  0

CalendarWeekly544
FromDate      ToDate
02/27/12              03/04/12
03/05/12       03/11/12
03/12/12              03/18/12
03/19/12       03/25/12
03/26/12       04/01/12
04/02/12              04/08/12
04/09/12              04/15/12
04/16/12              04/22/12
04/23/12        04/29/12

Desired Output
           ToDate         CreditBalance
       03/25/12          -110.00
       04/01/12          -110.00
      04/08/12          -110.00
        04/15/12          -110.00
      04/22/12          -110.00
        04/29/12          -110.00


This is how I wrote the select :
Select  billno, balance, ToDate
from(SELECT   Billno,ToDate,  sum([Amount]) as Balance
        FROM[TempReserve] t
        inner join CalendarWeekly544 C
            on t.SaleDate <= ToDate
            where ToDate <= getdate() --@CurrentDate
       --and Billno = '213830-1'
        Group by Billno, ToDate
        )as B  
where Balance <0
order by BillNO, ToDate

The code works but it is taking forever and when I just select the subquery I created millions of records.  How can I only select credit balances by week efficiently?    I can't sum all of the amounts first because I won't know what date the record had a negative balance and for how long it stayed negative.  

PS. Shooting the person who made the request is not an option.:)
LenTompkinsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
any index on the field ToDate?
what does the explain plan look like?
0
 
LenTompkinsAuthor Commented:
Thanks,  I had an index on ToDate and I added an index on BillNO and Grouping.  I had never looked at execution plans, but I read about them and saw that the order by clause was taking 82 % of the time.  When I eliminated that because it is eventually going into a table, the time came down to a minute 30 seconds which is acceptable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.