How to write an efficient select statement

Posted on 2012-08-21
Last Modified: 2012-08-22
I am trying to write a select statement that will write only credit balance records by week.

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

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.:)
Question by:LenTompkins
    LVL 142

    Accepted Solution

    any index on the field ToDate?
    what does the explain plan look like?

    Author Closing Comment

    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.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now