• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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.:)
0
LenTompkins
Asked:
LenTompkins
1 Solution
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now