CODEWITHSUNDEEP
sqlsql-serverloopssql-server-2005
jmich738
jmich738

Reputation: 1675

Replacing a loop query

I have the following tables set up:

   CREATE TABLE app_detail
(
CustID1 int,
CustID2 int,
AppDate datetime
)

CREATE TABLE inv_detail
(
CustID1 int,
CustID2 int,
PostDate datetime,
ClearDate datetime,
Amt float
)

INSERT INTO app_detail
VALUES(583,246,'2013-04-30 00:00:00.000')
INSERT INTO app_detail
VALUES(583,246,'2015-06-17 00:00:00.000')

INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000',NULL,17.56)
INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000','2013-12-31 00:00:00.000',667.97)
INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000','2014-10-05 00:00:00.000',3.96)
INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000',NULL,48.40)
INSERT INTO inv_detail
VALUES(583,246,'2013-01-08 00:00:00.000','2013-12-31 00:00:00.000',332.03)
INSERT INTO inv_detail
VALUES(583,246,'2013-01-08 00:00:00.000','2013-12-31 00:00:00.000',63.10)


INSERT INTO inv_detail
VALUES(583,246,'2013-07-09 00:00:00.000',NULL,1062.29)
INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000',NULL,17.56)
INSERT INTO inv_detail
VALUES(583,246,'2013-04-05 00:00:00.000',NULL,48.40)

select * from app_detail
select * from inv_detail

I would like to get the following output:

SELECT
 '583' AS CustID1
,'246' AS CustID2
,'2013-04-30 00:00:00.000' AS AppDate
,'1133.02' AS TotalAmount

UNION
SELECT
 '583'
,'246'
,'2015-06-17 00:00:00.000'
,'1128.25'

CustID1 CustID2 AppDate    TotalAmount
583      246    2013-04-30  1133.02
583      246    2015-06-17  1128.25

The first tables contains data on customers making an application on a particular date. The second table has the invoicing details for those customers, when the invoice was send and when it was paid (cleared). I want to know the amount of invoicing the customer had outstanding at the application date. If the invoice was paid, then there will be a date in the ClearedDate column. If the invoice was never paid, it will be NULL.

The only way I have thought about doing it is via a loop where I just pass one AppDate at a time to the WHERE clause. But I was hoping I can void that.

Any help would be appreciated.

Upvotes: 2

Views: 55

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Borrowed from Blorgbeard's answer to show how this could be done without the sub-query.

DECLARE @app_detail TABLE (CustID1 int,CustID2 int,AppDate datetime)
DECLARE @inv_detail TABLE(CustID1 int,CustID2 int,PostDate datetime,ClearDate datetime,Amt float)

INSERT INTO @app_detail VALUES(583,246,'2013-04-30 00:00:00.000')
INSERT INTO @app_detail VALUES(583,246,'2015-06-17 00:00:00.000')

INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000',NULL,17.56)
INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000','2013-12-31 00:00:00.000',667.97)
INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000','2014-10-05 00:00:00.000',3.96)
INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000',NULL,48.40)
INSERT INTO @inv_detail VALUES(583,246,'2013-01-08 00:00:00.000','2013-12-31 00:00:00.000',332.03)
INSERT INTO @inv_detail VALUES(583,246,'2013-01-08 00:00:00.000','2013-12-31 00:00:00.000',63.10)
INSERT INTO @inv_detail VALUES(583,246,'2013-07-09 00:00:00.000',NULL,1062.29)
INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000',NULL,17.56)
INSERT INTO @inv_detail VALUES(583,246,'2013-04-05 00:00:00.000',NULL,48.40)

select a.*, sum(Amt) AS [Total]
from @app_detail a
LEFT JOIN @inv_detail i ON i.CustID1=a.CustID1
    AND i.CustID2=a.CustID2
    AND i.PostDate <= a.AppDate
    AND (i.ClearDate is null or i.ClearDate > a.AppDate)
GROUP BY a.CustID1,a.CustID2,a.AppDate

Upvotes: 2

Blorgbeard
Blorgbeard

Reputation: 103555

You can do this using outer apply instead of a manual loop:

select a.*, due.Total
from app_detail a
outer apply (
    select sum(Amt) [Total]
    from inv_detail i
    where 
        i.CustID1=a.CustID1 and i.CustID2=a.CustID2 and 
        i.PostDate <= a.AppDate and (i.ClearDate is null or i.ClearDate > a.AppDate)
) due

It returns the correct results for me, after I removed two duplicate rows from your test data (17.56 and 48.40 both had two entries).

This is not a particularly efficient method - it will result in a table scan (or possibly index scan, if you have an appropriate index) of inv_detail for every row in app_detail. However, I don't believe there's a way around that in this case - it's not a simple aggregate, since a row from inv_detail may be involved in the calculation for many rows of app_detail.

Upvotes: 2

Related Questions