LenTompkins
asked on
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.:)
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.:)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER