Allocate Unclassified Payments to Classified Transactions to create Aged Debt Report

• aslam.jeewa

  SSC Enthusiast

  Points: 128

  More actions

  September 13, 2013 at 6:44 am

  #298770

  I Have a Transaction Table that contains both Debits & Credits with the following structure:

  DECLARE @Table1 TABLE

  (Account varchar(40),

  Service varchar(40),

  DateCol datetime,

  amount money)

  I need to work out Aged Debt. I have used

  http://www.sqlservercentral.com/Forums/Topic503770-149-1.aspx#bm503797

  to solve most of the issue but I have a twist as follows :

  •Payments are allocated to oldest balance first. - DONE using SQL from above link

  •If payments are split by service then they are allocated in those exact amounts to each service. - DONE using SQL from above link

  •If there is no service description on the payment then the allocation is made proportionally to the outstanding balance per service. - Creating a problem for me.

  I need to do a form of allocation of Payments as per

  http://www.sqlservercentral.com/Forums/Topic1180765-391-1.aspx

  but I am in urgent need of help.

  There are too many "no service description" payments transactions & I am finding the allocation of these payments challenging. Any assistance will highly appreciated.

  SQL I am currently using is :

  --start cleanup process

  DECLARE @g DATETIME --just to know how fast this is

  SET @g=GETDATE();

  DROP TABLE #cctemp

  DROP TABLE #ccbal

  DROP TABLE #CCTEMP_original

  DROP TABLE ddtemp

  DECLARE @dDateTime DATETIME = '2013-04-30'

  Use SQL_Example

  DECLARE @Table1 TABLE

  (Account varchar(40),

  DocType varchar(40),

  DateCol datetime,

  amount money)

  INSERT INTO @Table1 ( DATEcol,Account,DOCTYPE, Amount)

  (SELECT [TransDate]

  ,[AccountNo]

  ,b.[ServiceDescription]

  ,sum(CONVERT(money,[Amount]))

  FROM [SQL_Example].[dbo].[Transactions] a,

  [SQL_Example].[dbo].[Service Codes] b

  where a.[Service] = b.[ServiceCode]

  and CONVERT(money,[Amount])>0

  and [TransDate] <= @dDateTime

  group by [AccountNo],b.[ServiceDescription],[TransDate])

  UNION ALL

  (SELECT [TransDate]

  ,[AccountNo]

  ,b.[ServiceDescription]

  ,sum(CONVERT(money,[Amount]))

  FROM [SQL_Example].[dbo].[Payments] a,

  [SQL_Example].[dbo].[Service Codes] b

  where a.[Service] = b.[ServiceCode]

  and [TransDate] <= @dDateTime

  group by [AccountNo],b.[ServiceDescription],[TransDate])

  order by [AccountNo],b.[ServiceDescription],[TransDate]

  --first some test data

  CREATE TABLE #CCTEMP_original(memberID varchar(40), ServiceID varchar(40), cashflow money, postdate DATETIME)

  INSERT #CCTEMP_original

  SELECT Account,DocType,amount,DateCol

  FROM @Table1 order by DATEcol;

  CREATE CLUSTERED INDEX uci_cctempO ON #CCTEMP_original(memberID,ServiceID,postdate)

  SELECT memberID,

  ServiceID,

  SUM(CASE WHEN cashflow>0 THEN cashflow ELSE 0 END) AS debit,

  ABS(SUM(CASE WHEN cashflow>0 THEN 0 ELSE cashflow END)) AS credit,

  SUM(cashflow) AS bal

  INTO #ccbal

  FROM #CCTEMP_original

  GROUP BY memberID,ServiceID

  CREATE UNIQUE CLUSTERED INDEX uci_ccb ON #ccbal(memberID,ServiceID)

  SELECT *, CAST(0 AS money) AS runbal

  INTO #cctemp

  FROM #CCTEMP_original

  WHERE EXISTS (SELECT NULL FROM #ccbal WHERE #ccbal.memberid=#CCTEMP_original.memberID AND #ccbal.Serviceid=#CCTEMP_original.ServiceID)

  CREATE CLUSTERED INDEX uci_cct ON #cctemp(memberid, serviceID, postdate)

  --create a running grouped total of the debits:

  DECLARE @prevMemberID varchar(40)

  SET @prevMemberID=-1;

  DECLARE @prevServiceID varchar(40)

  SET @prevServiceID=-1;

  DECLARE @runbal money

  SET @runbal =0;

  DECLARE @dummy money

  SET @dummy=0;

  UPDATE #CCTEMP

  SET @runbal=runbal=CASE WHEN @prevmemberID=memberid AND @prevServiceID=serviceid THEN @runbal ELSE 0 END + cashflow,

  @dummy=@runbal,

  @prevmemberID=memberID,

  @prevServiceID=ServiceID

  FROM #CCTEMP WITH (INDEX(uci_cct),tablockX)

  --create the aging data

  SELECT MemberID, ServiceID, SUM(CashFlow) CashFlow, DATEDIFF(DAY,postDate + 1,@dDateTime)/30*30 DaysAged

  INTO DDtemp

  FROM #cctemp

  GROUP BY MemberID, ServiceID, DATEDIFF(DAY,postDate + 1,@dDateTime)/30*30

  ORDER BY MemberID,ServiceID